I've seen this a lot where people (including me a long time ago) confuse time and duration.
Technically, 24:00:00 doesn't exist. 23:59:59 is 1 second before midnight. Add 1 more second, and you get 00:00:00 (midnight).
Subtracting 2 date/times and you get a duration. The difference between 8:00:00 am and 10:30:00am is 2.5 hours or 2 hours and 30 minutes, but NOT 2:30!
You can
never write the difference as 2:30:00.
Access is pretty good at doing date/time math.
Try:
Code:
IIF(([dbo_orderheader].[ord_origin_latestdate])-([dbo_orderheader].[ord_bookdate])<24, "Yes", "No")
In Access I would use the function DateDiff()
the following are equivalent:
Code:
IIF(((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))/60)<24, "Yes", "No")
Code:
IIF((DateDiff("n",[dbo_orderheader].[ord_origin_latestdate]),([dbo_orderheader].[ord_bookdate]))<1440, "Yes", "No")
(there are 1440 minutes in 24 hours...)