customer order cut off times and dates

Occasional Contributor

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.Screenshot (28).png

4 Replies

@Ivan__Torr 

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)

 

#Isladogs
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

@Ivan__Torr 

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