Jul 29 2021 06:39 PM
I am in the process of creating an online ordering system for a restaurant. I have all my table, forms ets setup and working and the ordering system works. However, I now need to add options where if the customer has a cutoff time of eg 19.00 on any day (eg monday) and i order after 19.00 then the delivery date is set to the wednesday. Further, id that supplier does not deliver to me on a wednesday it is kicked off to the next delivery date.
I have added yes/no options to my TblSupp ( Suppliers) but cant think of where to start.
I have basic Vba experience but any help would be appreciated.
Jul 30 2021 12:05 AM
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)
Jul 30 2021 03:54 AM
Jul 30 2021 05:03 AM
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
Jul 30 2021 05:08 AM
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