convert date stamp to previous date

[favor08]

7/19/2007 12:46:30 am. is in a field called CmplteDte. It is a date stamp of when the user completed the item. I need the time for a report but for a form that the supervisors use to QA the previous days work I format the date to 7/19/2007 using a query Format([CmplteDate],"mm/dd/yy") so that they see everything for that date and not just that time.

My questions is that when work is stamped after 12 midnight it date stamps to the next day, How do I convert these dates back to the previous date so that the date says 7/18/2007. The items are still the previous days work even though the user didn't complete the item until the next day. It would be from the time frame of 12:00 am to 4:00 am that needs to be converted back to the previous day.

Here is an example

7/19/2007 12:46:30 AM i want to formatt to 7/18/2007.
7/19/2007 1:18:52 AM I want to formatt to 7/18/2007
7/18/2007 8:59:49 AM formatted to 7/18/2007
7/18/2007 8:25:49 PM formatted to 7/18/2007
7/18/2007 8:15:55 PM formatted to 7/18/2007
7/18/2007 7:58:01 PM formatted to 7/18/2007
7/18/2007 7:42:10 PM formatted to 7/18/2007

[nico5038]

Try to use as a field in your query:

Format([AanvraagDatum]-0,2000001;"yyyy-mm-dd") ' European setting with comma and semicolon as separator character

Format([AanvraagDatum]-0.2000001,"yyyy-mm-dd") ' US setting with dot as decial separator and comma as listseparator

As the time is reflected as a decimal fraction of the datenumber, you can subtract 0,2 to have a 4 hrs correction (24hrs=1day so 6 hrs = 0,2 day) I've added a very small fraction to include 4:00, but you can modify that when exactly 4:00 is excluded into 0,2

Nic;o)

[cactusdata]

I would just shift the recorded time by four hours:

Select *, DateAdd("h", -4, CmplteDte) As WorkDte From tblYourTable

To remove the time portion, apply Int:

Select *, Int(DateAdd("h" , -4, CmplteDte)) As WorkDte From tblYourTable

/gustav

PS: Nico, is that you from EE?