Forum Discussion
customer order cut off times and dates
Unfortunately, the image isn't clear enough to read on my tablet so you'll need to adapt this to match your field names.
Can you use something like this expression for the delivery date?
IIf(OrderTime>#17:00#, Date()+2, Date()+1)
Thanks for that but how would I then manage the no delivery dates. So if the next day is not a delivery date and order time is greater than current time. It still needs to kick the delivery to date() +2. I have the 7 days set as boolean.
Thanks
Ah you didn't mention that point originally.
Depending on what determines a business day, you might be able to do this by adding AND or OR sections to the IIf criteria.
For example if business days are Mon-Fri only, then you could use the Weekday function for this. See MS Access: Weekday Function (techonthenet.com).For example Sunday = 1 through to Saturday = 6 (though this may be different for you).
Anyway, assuming the above is true, only orders made on Mon-Thur before say 19:00 get next day delivery. If so then try something like this:
IIf((Weekday(Date())>=2 And Weekday(Date())<=5 And OrderTime<#19:00#, Date()+1, Date()+2)However, that doesn't take into account public holidays which may also prevent next day delivery.
Anything more complex than the above would best be done by creating a user defined function.
Hope that helps
Sorry. Missing bracket. It should have read:
IIf((Weekday(Date())>=2 And Weekday(Date())<=5 And OrderTime<#19:00#), Date()+1, Date()+2)Notice that I reversed the original True/False outcomes. Due to the use of AND, all conditions must be true to satisfy the True part i.e. next day delivery
