Weekday Code

rohit_ghosh · June 20th, 2007, 05:08 AM

Option Compare Database
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim stDocName As String
Dim stLinkCriteria As String

D = Format(Now, "DDDD")
C = Format(Now, "D")

If D = "Monday" Or _
D = "Tuesday" Or _
D = "Wednesday" Or _
D = "Thursday" Or _
D = "Friday" And C = "1" Then

stDocName = "FrmBD1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'End If

'If D = "Monday" And Format(Now, "DD") = "02" Or Format(Now, "DD") = "03" Then

'stDocName = "tblAshim"
'DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End If

End Sub

Hi,

I have written the above mentioned code for opening a form on 1st of every month in case its a weekday else if it is 2nd or 3rd of any month with weekday "Monday". The programme is working on all days....Can somebody help me modify this code to work on the above criteria. Help will be greatly appreciated.

rgerald · June 20th, 2007, 09:04 AM

All you need to do is to add parentheses as follows:

Code:

If ( D = "Monday" Or _
D = "Tuesday" Or _
D = "Wednesday" Or _
D = "Thursday" Or _
D = "Friday" ) And C = "1" Then

Rand

robzyc · June 21st, 2007, 03:03 AM

Or you could try:

Code:

Dim wkday as Integer
wkday = Weekday(Date)
Dim dayNum as Integer
dayNum = Day(Date)

If dayNum = 1 Then
    If (wkday > vbSunday And wkday < vbSaturday) then
    'Do your stuff for the 1st
    End If
ElseIf dayNum = 2 or dayNum = 3 Then
    If wkday = vbMonday Then
    'Do your 2nd & 3rd stuff on Mondays
    End If
Else
    'Whatever
End If

This should execute faster, and IMO, is easier to read. Its obvious what each IF statement is doing.

Hope this helps.
Best Regards,
Rob

rohit_ghosh · June 21st, 2007, 08:48 AM

Thanks a ton Gerald and Rob...

The code seems to be working fine with monday in it but if i add tuesday, it fails...am i doing something wrong...

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Dim stDocName As String
Dim stLinkCriteria As String

Dim wkday As Integer
wkday = Weekday(Date)
Dim dayNum As Integer
dayNum = Day(Date)

If dayNum = 2 Then
    If (wkday > vbSunday And wkday < vbSaturday) Then
    stDocName = "tblAshim"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
ElseIf dayNum = 4 Then
    If wkday = vbMonday Then
    stDocName = "tblAshim"
DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
ElseIf dayNum = 3 Or dayNum = 4 Then
    If wkday = vbTuesday Then
    stDocName = "tblAshim"
DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Else

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End If

End Sub

robzyc · June 21st, 2007, 09:07 AM

rohit_ghosh,

What error is it giving you?
The Tues code will not execute if it is the 4th day of the month with your current code as it will be picked up by the 2nd ElseIf statement.

Replace the "Else" above "Exit_Command1_Click" with End If, you have encases your error handler in the main code body! Dont forget to remove the "End If" above the "End Sub".

Regards,
Rob

rohit_ghosh · June 21st, 2007, 09:21 AM

Hi Rob,

I have figured out the tusday thing....I am not able to remove and replace the else and end if as I am getting Block if with end if error...if i go by the way you suggested. Also where can i add a msgbox in this code to tell the users that the code wont run today as it is not the appropriate date if the code doesnt fulfills the required conditions...again many thanks for helping me here.

robzyc · June 21st, 2007, 09:33 AM

rohit_ghosh,

Try this code, I have made a few slight modifications:

Code:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim stLinkCriteria As String

Dim wkday As Integer
wkday = Weekday(Date)
Dim dayNum As Integer
dayNum = day(Date)

If dayNum = 2 Then
    If (wkday > vbSunday And wkday < vbSaturday) Then
        stDocName = "tblAshim"
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf dayNum = 3 Or dayNum = 4 Then
    If wkday = vbMonday Then
        stDocName = "tblAshim"
    ElseIf wkday = vbTuesday And dayNum = 4 Then
        stDocName = "tblAshim"
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
    'Code does not meet above.
    MsgBox "Bad date!", _
            vbCritical + vbOKOnly, _
            "Title"
End If

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
End Sub

Regards,
Rob

rohit_ghosh · June 21st, 2007, 10:19 AM

Hi Rob...Thanks for your help...

But the code is not working if i change the system date to 4th of June..

rohit_ghosh · June 21st, 2007, 10:28 AM

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim stLinkCriteria As String

Dim wkday As Integer
wkday = Weekday(Date)
Dim dayNum As Integer
dayNum = Day(Date)

If dayNum = 2 Then
    If (wkday > vbSunday And wkday < vbSaturday) Then
        stDocName = "tblAshim"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

ElseIf dayNum = 4 Then
    If wkday = vbMonday Or wkday = vbTuesday Then
        stDocName = "tblAshim"
    ElseIf wkday = vbTuesday And dayNum = 3 Then
        stDocName = "tblAshim"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Else
    'Code does not meet above.
    MsgBox "Today is not SBDOM!", _
            vbCritical + vbOKOnly, _
            "Title"
End If

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
End Sub

here is the code I have now....This code should only work if its monday the 3rd or monday the 4th. If its 2nd of any weekday. if its tuesday the 3rd or tuesday the 4th....

rohit_ghosh · June 21st, 2007, 10:37 AM

I have resolved the conflict in the code thanks to Rob. Rob thanks for your help and patience...

The final code is..

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim stLinkCriteria As String

Dim wkday As Integer
wkday = Weekday(Date)
Dim dayNum As Integer
dayNum = Day(Date)

If dayNum = 2 Then
    If (wkday > vbMonday And wkday < vbSaturday) Then
        stDocName = "tblAshim"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

ElseIf dayNum = 4 Then
    If wkday = vbMonday Or wkday = vbTuesday Then
        stDocName = "tblAshim"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    ElseIf wkday = vbTuesday And dayNum = 3 Then
        stDocName = "tblAshim"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Else
    'Code does not meet above.
    MsgBox "Today is not SBDOM!", _
            vbCritical + vbOKOnly, _
            "Title"
End If

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
End Sub