Pesky Date/Time problems

[Stacey]

I have a query based on a non updateable linked table. In the table the date format is Date/time (06/08/2004 10:07:07). I have a report based on the query. In the report, I would like to group and subtotal by date. Unfortunately, it is looking at each date/time as a separate group. How can I make the report group and subtotal based on the date alone, without being able to change the format of the table's field?

Thanks so much for your help, these date formats get me every time.

Thanks again
Stacey

 

[FoFa]

I would create a calculated field in the query the report is based on, and use FORMAT([MyDate],"mm/dd/yyyy") (I use USA date formats). There are probably 10 other ways to do it also knowing MSAccess.

 

[Stacey]

That seemed to solve the grouping problem on the report, but now my date prompt in the criteria field for the date is not working. It is bringing back dates outside the range specified. Any ideas?

 

[namliam]

You should put the criteria on a hidden field, which is unformated.
And use the (visible) formatted fields only for grouping and showing puposes

Regards

 

[Oy Vey]

The Format method converts the date to a string, which tends to confuse things. Try CDate(Format([datefield], "Short Date")). The net effect is that you get "06/09/2004 00:00:00AM" but the time will not be displayed.