Hi,
I am working in Access '97.

I have several forms and reports, which have 2 date fields. The first field (a textbox - Audit_Date) pulls a date from the database. The second field (Next_Audit) uses the following:

=DateAdd("m",1,Audit_Date)

to automatically schedule the next audit one month from 'Audit Date'.

My problem is how can I prevent it from scheduling audits on weekends and holidays?

I have tried using various permutations of 'datepart' to try and exclude Saturday and Sunday, but to no avail.

It's been years since I created anything in Access, and I learned on 2.0, so I'm pretty rusty. Any help would be greatly appreciated.

Kelly Smith

Weekends are not difficult. Try looking at the Weekday() function. You feed it the date, and a constant that tells it which day you want to start the week (the default is vbSunday). It gives you an integer that corresponds to the date of the week. If you use the default, then days that return 1 and 7 are weekends.

For holidays, you may need to create a table or do more extensive hard coding.

Hi Chris,
Thanks for your prompt response. I have already tried what you mention however, and have not been able to make it work.

This is the latest piece of code I've tried to use:

'What day is the next audit scheduled for?
Dim newdate As Date

Next_Audit.Text = DateAdd(m, 1, Audit_Date.Text)


'If the next audit is scheduled for Sunday
If WeekDay(Next_Audit) = 1 Then

'then add one day to it
newdate = DateAdd(d, 1, Next_Audit)

'If the next audit is scheduled for Saturday
ElseIf WeekDay(Next_Audit) = 7 Then

'then add two days to it
newdate = DateAdd(d, 2, Next_Audit)

End If

'and set the ‘next audit’ textbox to the 'newdate' - Monday
Next_Audit.Text = newdate

I've tried this in various events (form load, form open, next_audit gotfocus, next_audit enter). No matter where I put it, I get errors on one kind or another. (I've also tried Dimming newdate just as is (not as a date) and got type mismatch errors.)

I will keep plugging away at it, but if anyone else has ideas, or knows what I'm doing wrong, please let me know.