I have a table of delivery dates for customer companies, set up with checkbox fields for Mon - Fri. Also a table of holidays with specific dates (7/4/2004, etc.). I need to calculate the next delivery date; for instance if today is Friday and delivery dates are Mon, Wed, and Fri then the next delivery day would be Mon. Can't figure out how to get the date of the next delivery day. Not all companies would have the same delivery days and not all companies would have the same number of deliveries per week. Also, if the next delivery date falls on a holiday, as entered in the holidays table, then that delivery day would move to the next delivery day. I can't seem to get a handle on this. Does anybody have any ideas?

The WeekDay function will return the day of the week for the passed in date. Therefore if each of your check boxes are assigned a number (e.g. Monday = 1, Tuesday = 2, etc.) then you can use the WeekDay function to test if the date is one of the selected days. If it is not then you can use the DateAdd function to increment the date until it is an acceptable date.

The syntax of the WeekDay functions is:

WeekDay(date, [firstdayofweek])

The date part should be an expression that can represent a date (date, string or numeric varable).
The firstdayofweek part is optional and if included specifies the first day of the week, if it is omitted then vbSunday is assumed. Any of the following constants can be used as the firstdayofweek part:

vbSunday
vbMonday
vbTuesday
vbWednesday
vbThursday
vbFriday
vbSaturday

The DateAdd function returns a date and it's syntax is:

DateAdd(interval, number, date)

Where the interval part is one of the following:

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

The number part is the number of intervals you want to add and the date part is the date you want to add to.

I hope this helps.

Regards
Owain Williams