Calculating Date based on user inputted date

[mbaddar]

Hi,

Can anyone point me in the right direction? I'm looking at a "First Session Scheduled Date" field in my table. I want to take this date and get the date of the first Friday after this date.
I looked at the VB Date functions and I didn't see anything that would make it very easy for me.

Can anyone make any suggestions?

Thanks,
MBaddar

 

[pgk]

Hi,
Try out the function below by pasting it in some module. Pass it the First Scheduled Date and it will return the first Friday after that.

Note: If Fist Scheduled Date itself is a Friday, it will return the date of the next Friday.

Public Function getDate(ByVal dat As Date) As Date
' Returns the next Friday from the passed date
Select Case Weekday(dat)
Case 1 'Sunday
getDate = DateAdd("d", 5, dat)
Case 2 'Monday
getDate = DateAdd("d", 4, dat)
Case 3 'Tuesday
getDate = DateAdd("d", 3, dat)
Case 4 'Wednesday
getDate = DateAdd("d", 2, dat)
Case 5 'Thursday
getDate = DateAdd("d", 1, dat)
Case 6 'Friday
getDate = DateAdd("d", 7, dat)
Case 7 'Saturday
getDate = DateAdd("d", 6, dat)
End Select
End Function
Hope it helps. Let me know what happens.
With regards,
PGK

 

[scriverb]

The following can be used as a control source to calculate this field based upon the date in YourDateControl on your form and display it on your form:

=DateAdd("d", Switch(WeekDay(Me![First Session Scheduled
Date]) < 6, 6 - WeekDay(Me![First Session Scheduled Date]),
WeekDay(Me![First Session Scheduled Date]) > 6, 6, WeekDay
(Me![First Session Scheduled Date]) = 6, 7), Me![First
Session Scheduled Date])

Or you can use it to make an assignment to a variable or control in VBA code:
Dim vDateNextFriday as date
vDateNextFriday = DateAdd(&quot;d&quot;, Switch(WeekDay(Me![First
Session Scheduled Date]) < 6, 6 - WeekDay(Me![First Session
Scheduled Date]), WeekDay(Me![First Session Scheduled
Date]) > 6, 6, WeekDay(Me![First Session Scheduled Date]) =
6, 7), Me![First Session Scheduled Date])

Just another way of doing it. PGK's is just fine but this is an example of how to do it in a single expression.
Bob Scriver

 

[mbaddar]

Hi,

Thank you both. PGK's worked great but thanks Bob for your tip as well.

Thanks a lot,
MBaddar