Advanced date diff

[battenberg]

Hi,

I want to fire an event that will automatically, set a date 45 days into the future based on a recorded [datefield] in my database.

However, I want to know if this date falls on a saturday or sunday and if it does, return a warning.

can anybody point me in the right direction to get started on this code?

the code i have so far is this:

dDate = [ForcastDeliveryDate]
dLockDate = DateSerial(Year(dDate), Month(dDate), Day(dDate) + 45)
MsgBox "Lock Date: " & Format(dLockDate, "Long Date")

I would like an if statement to give me a second warning if this date falls on a weekend??

any thoughts?

Thanks & Regards

 

[KenHigg]

You should be able to use the Weekday() function to do this.

 

[battenberg]

thankyou... will research....

 

[boblarson]

Plus use DateAdd instead of going to all the trouble of using Date Serial.

dDate = DateAdd("d",45,ForecastDeliveryDate)

 

[battenberg]

Thanks Bob, that looks to be far more efficient code!

It should be possible to use a dlookup on my 'table of database defaults' to substitue an integer variable for the '45' in that statement???

 

[boblarson]

Yeah, you can substitute whatever number of days, including using a number variable to set the actual number, instead of hard-coding 45.

 

[Dreamweaver]

I use this in one program so if the date falls on a sat or sunday it gets moved back to friday

Nd = DateAdd("m", 1, DateSerial(Y, M, 1)) - 1
CWd = Weekday(Nd, vbMonday)
        'Now If the date falls on a sat or sunday move the day back to friday
    If CWd > 5 Then
        If CWd = 6 Then D = 1
        If CWd = 7 Then D = 2
        Nd = DateSerial(Y, M, Day(Nd) - D)
    End If
Me![txtStartDate] = Nd