Changing the date format & adding the date range to the header

[Guest]

I've created a database to track timecards. Two issues have come up that I
can't find answers for:

How do I change the date format without using the presets. I need to
display just the month and day in one report and yyyy/mm/dd in the database
whereever a date appears. I'm using Access 2003. Neither of those options
appear to be in the presets.

In one query I have set a date range selection, when the report is run the
user is prompted to enter "start date: mm/dd/yy" (which I'd like to change to
the above mentioned format of yyyy/mm/dd) then "end date: mm/dd/yy". I would
like to have the report header say Pay Period: YYYY/MM/DD to YYYY/MM/DD based
on the prompting for the report. Suggestions?

Thanks for the help
Pamala

 

[John Vinson]

I've created a database to track timecards. Two issues have come up that I
can't find answers for:

How do I change the date format without using the presets. I need to
display just the month and day in one report and yyyy/mm/dd in the database
whereever a date appears. I'm using Access 2003. Neither of those options
appear to be in the presets.

Well, then don't use the presets! They are there for convenience but
you can use any custom format you wish. Set the Format property of the
textbox to "mmmm d" (for October 6) in the first control - or mm/dd
for 10/06; and to "yyyy/mm/dd" in the other.

In one query I have set a date range selection, when the report is run the
user is prompted to enter "start date: mm/dd/yy" (which I'd like to change to
the above mentioned format of yyyy/mm/dd) then "end date: mm/dd/yy". I would
like to have the report header say Pay Period: YYYY/MM/DD to YYYY/MM/DD based
on the prompting for the report. Suggestions?

This is simpler if you use a Form to solicit the parameters. Use a
form, frmCrit let's say, with two unbound textboxes txtStart and
txtEnd; use criteria

= CDate([Forms]![frmCrit]![txtStart]) AND < DateAdd("d", 1, CDate([Forms]![frmCrit]![txtEnd]))

to cover the possibility that your table field contains a time value
and/or the user types in an odd date format. Then on the Form have two
textboxes - formatted yyyy/mm/dd - with control source

=[Forms]![frmCrit]![txtStart]

and txtEnd of course.

Put a command button on frmCrit to launch the report, and the user
need never even see the database window, just your switchboard form!

John W. Vinson[MVP]