General Date problem

[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

 

[Allen Browne]

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)

 

[MarkFrost]

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

 

[Allen Browne]

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 ...

 

[MarkFrost]

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.