General Date problem

  • Thread starter Thread starter MarkFrost
  • Start date Start date
M

MarkFrost

Hi

I have a table that lists the date and time that we receive orders, in a
general date format (20/11/2008 14:30:00). I then show if these orders were
despatched or not.

I need to create a query that will only pull in records between certain
dates and times.
Ie. If today is Thursday (20/11/2008), then show me all orders received
between Tuesday (18/11/2008) 16:30:00 and Wednesday (19/11/2008) 16:29:59.

This is kind of where I got to, but I am getting in above my head:

IIF(DatePart("w",Date())=5,Between
DateSerial(Year(Date()),Month(Date()),Day(Date())-2) And
DateSerial(Year(Date()),Month(Date()),Day(Date())-1)

This gives me all of the orders received on the two days, but I don't have
an idea how to build in the time parameters.

I can do this manually by typing the dates/times in, but I need it to be
dynamic. The database will run over night and then email an exception report,
for orders not despatched, through reporting services each morning.

Cheers

Mark
 
So you always want the date range from 4:30pm 2 days ago to 4:29:59
yesterday.

Try criteria of:
Between DateAdd("s", 59400, Date() - 2) And DateAdd("s", 59399, Date() - 1)
 
Hi Allen

I will give this a go.

To answer your question:

I do always want this date range with the exception of the report run on a
Tuesday. This will need to look at orders received from Friday 16:30:00 to
Monday 4:29:59.

This is why I was trying to bring in a day of the week.

Regards

Mark
 
Not sure if this is exactly what you want, it this subtracts 4 days if today
is Sunday - Tuesday, otherwise 2:

Between DateAdd("s", 59400, Date() - IIf( Weekday(Date()) <= 3, 4, 2))
And ...
 
Thanks Allen, it is nearly there.

There is another variable that needs to be built in:

Monday's Report (weekday 2) needs to be between Thursday 16:30:00 and Friday
16:29:59.

So I have amended it to this:

Between DateAdd("s",59400,Date()-IIf(Weekday(Date())=3,4,2)) And
DateAdd("s",59399,Date()-IIf(Weekday(Date())=2,3,1))

I hasn't given me any errors at the minute. So I think that it may just work.

Thanks for your help.
 
Back
Top