Windows XP Date/Time qry

[ahamer]

First this is my first try at posting a question so correct me if I'm not following the correct procedure.
I have qry in Access and one of the fld's is a date time fld and I dont want to see the time. I want to group on the date only and I cant figure out how to build the expression so the qry only returns the date without the time and groups the dates together?

 

[Me__2001]

i've done a bit of work with access

to get the date in a report i used

=Now()

and for the time

=Time()


any use to you ?

 

[ahamer]

So in the design view of my qry are you saying that I build an expression or just use =Now() in the criteria?

By the way a tequila shot would probably be better than trying to do my analyst work!

 

[ahamer]

by the way I'm not creating a report only a display qry...

 

[Me__2001]

whats the purpose of the query ?

 

[ahamer]

most of the tlb's that we qry daily all have a date field that is formatted as date/time so when you pull in that fld in your qry you get somthing like 1/23/2006 8:41:01 AM. This displays every transaction to the tbl in seconds, minutes etc... We typically want to see the data rolled up or grouped by date and not the time.

 

[Me__2001]

what i would suggest is to use a field for the date and one for the time, i dont think you can qry just part of a field but i could be wrong as im no expert on this

 

[ahamer]

I think I may have the answer... You create an expression DateValu([Date]) and this will return the date only and not the time... Thanks for you help..

 

[sarong17uk]

A quicker solution would be to set the Format attribute; this can be applied to all the objects of Access. The table can hold date and time but queries, forms and reports can display as you choose; For example, the date 16th February 2006 can be displayed as follows: set the format to dd-mm-yy to get 16-02-06; set it to dd/mmm/yyyy to get 16/Feb/2006. Right click to get properties if you cannot see the option to set the Format attribute. Hope this helps.

 

[ahamer]

Thanks..but the problem with that attibute is that you will still get multiple rows of data for the same item if you attempt to group or sum the record for each time the time changed for that transction?

 

[sarong17uk]

If grouping or summing is required, then your solution DateValue([date]) will work. You can also use DatePart(intercval,[date]) if you want to group to smaller subsets eg by the hour etc. Good luck anyway!

 

[ahamer]

Thanks I wasnt aware of that expression and I will be able to use that as well....

Thanks