Determine which day of the week

[deejay_totoro]

Hello,

I want to set a date in the field [MyFieldStartDate], from which queries will be run. The start date cannot be on a Saturday or Sunday. (For the moment I am not concerned with national holidays.)

If someone could help, that would be great!

Here is psuedo code if what I want to do:

If strToday = (Saturday or Sunday) Then Set the date of [MyFieldStartDate] to (the date of Friday of last week)

However!

If strToday = Any other day of the week, then [MyFieldStartDate] is (yesterdays date)

Many thanks,

dj_T

 

[Matt Greatorex]

If I understand the logic, then the way of handling all days except Sunday is to revert to the previous day? If so, is something like this what you need?

Dim ld_Today as Date

ld_Today = CDate(strToday) ' -- Converts the string to a date

If Format(ld_Today,"DDDD") = "Sunday") Then 

          [MyFieldStartDate] = DateAdd("d", 1, ld_Today - 2) ' -- 2 days before

Else

          [MyFieldStartDate] = DateAdd("d", 1, ld_Today - 1) ' -- 1 day before

End If

 

[billyr]

What do you want to happen if strToday is Monday?

 

[Matt Greatorex]

What do you want to happen if strToday is Monday?

Fair point . That's what i get for rushing.

Make that

Dim ld_Today as Date

ld_Today = CDate(strToday) ' -- Converts the string to a date

If Format(ld_Today,"DDDD") = "Sunday") Then 

          [MyFieldStartDate] = DateAdd("d", 1, ld_Today - 2) ' -- 2 days before

ElseIf Format(ld_Today,"DDDD") = "Monday") Then 

          [MyFieldStartDate] = DateAdd("d", 1, ld_Today - 3) ' -- 3 days before

Else

          [MyFieldStartDate] = DateAdd("d", 1, ld_Today - 1) ' -- 1 day before

End If

 

[Rich]

What about Saturdays? and why not just use the Weekday function?

 

[billyr]

Rich is right. Sounds like a case statement would be the simple route.
Select case weekday(ld_today)
case 1
........
case 2
........
case 7
........
case else
........
end select

 

[raskew]

Hi -

You might give this a try:

Function BackBusDays2(pStart As Date, _
                      pNum As Integer) As Date
'*******************************************
'purpose:   Output date if pNum business
'           days are subtracted from pStart.
'coded by:  raskew
'Inputs:    from debug (immediate) window)
'           ? BackBusDays2(#2/22/06#, 3)
'Output:    2/17/06
'*******************************************
                      
Dim dteHold As Date
Dim i As Integer
Dim n As Integer

    dteHold = pStart - 1
    n = 0
    Do While n < pNum
       i = WeekDay(dteHold)
       n = n + IIf(i = 1 Or i = 7, 0, 1)
       dteHold = dteHold - 1
    Loop
    
    BackBusDays2 = dteHold + 1
End Function

HTH - Bob

 

[Matt Greatorex]

What about Saturdays? and why not just use the Weekday function?

As far as Saturday goes, treating it like another day and just using the previous one would work, as it would give the Friday.

Didn't know about the weekday function.