Re-Defining Dates (really!)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working on a database that needs to consider anything between 3 pm and 3
am the same date --

e.g., December 25: inluces both December 25 3 to midnight AND December 26,
12:00 AM to 3:00 AM

Any ideas? Thanks!
 
I' assuming that you mean between 3:01 am and 3 am. If not, what happens at 2
pm?

DateAdd("h", -3, [TheDateFeild])
 
This is a database where nothing happens in the day time!! Does this help?

December 25 = 3 PM December 25 through 3 AM December 26
December 26 = 3 PM December 26 through 3 AM December 27
 
This is what I'm thinking, but of course it's not working:

ReportDate: IIf([ctime]<12,[cdate],[cdate]-1)
 
Jerry Whittle gave you the answer or add to it like this ---
Format(DateAdd("h", -3, [TheDateFeild]),"mm/dd/yyyy")
 
This should still work if the date/time is in one field:

DateAdd("h", -3, [TheDateFeild])

If the date is in one field and time in another, sorry to hear that! If they
are both date/time fields, something like this might work:

DateAdd("h", -3, [cdate] + [ctime])
 
This is what I get with

ReportDate: DateAdd("h",-3,[cdate]+[ctime])

ReportDate Date Call Received Time Call Received
6/17/2007 12:44:00 AM 6/17/2007 3:44 AM
6/17/2007 8:00:00 PM 6/17/2007 11:00 PM

The report date for the 3:44 AM should be 6/16, not 6/17
 
Perfect!! ReportDate: DateAdd("h",3,[cdate]+[ctime])-1 worked!! Thank you,
Jerry and Karl!!!
 
I'm working on a database that needs to consider anything between 3 pm and 3
am the same date --

e.g., December 25: inluces both December 25 3 to midnight AND December 26,
12:00 AM to 3:00 AM

Any ideas? Thanks!
Click to expand...

DateValue(DateAdd("h", -3, [YourDateField]))

will return 12/25/2007 for the range #12/25/2007 03:00:00# to #12/26/2007
03:00:00#.

John W. Vinson [MVP]
 
Your formula will result in errors as you are subtracting a whole day.
It should be --
ReportDate: DateAdd("h",-3,[cdate]+[ctime])
See the .............. -3
 
Back
Top