Using IIf to change a date in query

  • Thread starter Thread starter Lee E. Johnson
  • Start date Start date
L

Lee E. Johnson

I would like to check a date/time in a query and if it is after midnight but
before 4:30am I want to subtract a day from it, else it stays the same. I
was looking at something like this (not that it works). How would I put
this into a query anyway?

IIf([OrderDateTime]<#4:30:00#,DateAdd("d","-1",[OrderDateTime]),[OrderDateTime])

Thanks,

Lee
 
Hi Lee E. Johnson,

You are close. The problem is that "#4:30:00#" will have a day associated
with it. If I am not mistaken, it will be today's date.

Try this,

SELECT IIf(Format([OrderDateTime],"hh:nn:ss")>"00:00:00" And
Format([OrderDateTime],"hh:nn:ss")<"04:30:00",DateAdd("d","-1",[OrderDateTime]),[OrderDateTime]) AS Expr1, Table1.OrderDateTime
FROM Table1;

Hope this helps.
 
Worked EXACTLY like I wanted. Thanks for the help.

Lee

JL said:
Hi Lee E. Johnson,

You are close. The problem is that "#4:30:00#" will have a day associated
with it. If I am not mistaken, it will be today's date.

Try this,

SELECT IIf(Format([OrderDateTime],"hh:nn:ss")>"00:00:00" And
Format([OrderDateTime],"hh:nn:ss")<"04:30:00",DateAdd("d","-1",[OrderDateTime]),[OrderDateTime])
AS Expr1, Table1.OrderDateTime
FROM Table1;

Hope this helps.


Lee E. Johnson said:
I would like to check a date/time in a query and if it is after midnight
but
before 4:30am I want to subtract a day from it, else it stays the same.
I
was looking at something like this (not that it works). How would I put
this into a query anyway?

IIf([OrderDateTime]<#4:30:00#,DateAdd("d","-1",[OrderDateTime]),[OrderDateTime])

Thanks,

Lee
Click to expand...
Click to expand...
 
Back
Top