1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2003 - Date Format in a Calculated Field

Discussion in 'Business Applications' started by Chris_E, Aug 15, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Chris_E

    Chris_E Thread Starter

    Joined:
    Feb 16, 2006
    Messages:
    360
    Good day guys

    My puzzler today (and has been for ages) is this:

    In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

    I therefore have a field called "DateDetails" in my report header whose ControlSource is:

    Code:
    ="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
    The dates actually derive from a table and the date format for both fields in that table is "General Date".

    The result is that the report shows the following as an example:


    Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

    BUT what I want is the following:

    Records Selected between 17/05/07 and 10/08/07

    or even nicer

    Records Selected between 17-May-2007 and 10-August-2007

    Can this be done in such a control?

    Chris
     
  2. Sponsor

  3. jimr381

    jimr381 Jim

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    Well you told it it was a Forms object before the first bang (!) and then specified in the question that it is a table. That might be the issue. Is your table called Reports? Ignore this I now reread it and saw you stated calling form in there.
     
  4. jimr381

    jimr381 Jim

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    There is a formatdatetime function which allows you to format different data sets different ways.
     
  5. Chris_E

    Chris_E Thread Starter

    Joined:
    Feb 16, 2006
    Messages:
    360
    That's right....

    When I said the dates come from a table, I meant that their source is in a table. They are displayed in a form (yes, confusingly called "Report") and the report looks at the chosen dates in that form and displays them on the report header.

    I'll look out how to use the "formatdatetime function", unless you have some quick pointers?

    Thanks

    Chris
     
  6. Chris_E

    Chris_E Thread Starter

    Joined:
    Feb 16, 2006
    Messages:
    360
    Sorted it out...

    The way I did it was as below:

    ="Records Selected between " & (Format(Forms!Reports!StartDate, "dd-mm-yy")) & " and " (Format(Forms!Reports!EndDate, "dd-mm-yy"))

    All done!

    Chris
     
  7. jimr381

    jimr381 Jim

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    Congrats. I was first thinking the format function as well. The format function is more robust and will take lots of different types of values. ;)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/610098