adjusted date query

  • Thread starter Thread starter eb1mom
  • Start date Start date
E

eb1mom

I am trying to create a query that will add 1 day to date
field. Shift is from 11 pm to 7 am. I have a date/time
field that needs to be adjusted. If time is between 11pm
and midnight the date/day needs to add 1. I tried formating
date field to hh.mm.ss and then a between query but, when I
type 23.00.00 I get a message that it is not a valid
number. So, I am stuck at starting gate, on how to do this.
Any suggestions or links to articles would be appreciated.
Thank-you
 
Maybe this gives you a hint

DateAdd("h",iif(Hour([DateField])=23,8;iif(Hour(DateField)<7,7-Hour([DateField]),Hour([DateField])))

Another way is to strip the date and add the time again

iif(Hour([DateField])>=23 or Hour([DateField])<7,7,Hour([DateField]))

- Raoul
 
Do you want to actually update the Table (which will mess up your data) or
you simply want to display the "Adjusted Date" in the Query?

My guess is to display only. In this case, you can simply use a calculated
Field in your Query:

Adjusted Date: DateValue( DateAdd("h", 1, [YourField]) )
 
Back
Top