Check if Date entered falls on Saturday or Sunday.

[Singh400]

Been googling for a while now, and I can't find any examples of what I want.

I have a form (frmBookings) and on this form there is a field (txtDateOfBooking) where a date is entered for whenever the person requires our services.

Now I need to check whether the date entered falls on a Saturday or Sunday, and if it does I want Access to alert me. But if I still require the date to be a Saturday or Sunday to let me do so.

The txtDateOfBooking is formatted as Medium Date.

 

[pbaldy]

Look at the Weekday function in Help.

 

[missinglinq]

Private Sub txtDateOfBooking_AfterUpdate()
 If Weekday(Me.txtDateOfBooking) = 1 Or Weekday(Me.txtDateOfBooking) = 7 Then
   MsgBox "The Booking Date of  " & Me.txtDateOfBooking & "  Falls on a  " & WeekdayName(Weekday(Me.txtDateOfBooking))
 End If
End Sub

 

[datAdrenaline]

I think I would use the BeforeUpdate event on the control, that way the user can respond and your code can react accordingly (ie: Cancel the updating of the control and undo the value)

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
    If Weekday(Me.txtDateOfBooking, vbSaturday) <= 2 Then
        
        Cancel = (MsgBox("The Booking Date is on a " & _
                         Format(Me.txtDateOfBooking, "dddd") & vbCrLf & vbCrLf & _
                         "Do you want to keep this date?" _
                         , vbYesNo + vbQuestion) = vbNo)
    
        If Cancel Then Me.txtDateOfBooking.Undo
    
    End If
End Sub

Hope that helps!

 

[Singh400]

Look at the Weekday function in Help.

Ah thank you for that! I prefer to learn this way, instead of nicking someones codes and making no attempt to understand it.

Private Sub txtDateOfBooking_AfterUpdate()
 If Weekday(Me.txtDateOfBooking) = 1 Or Weekday(Me.txtDateOfBooking) = 7 Then
   MsgBox "The Booking Date of  " & Me.txtDateOfBooking & "  Falls on a  " & WeekdayName(Weekday(Me.txtDateOfBooking))
 End If
End Sub

Thank you for that. Just tried it and while it does what I want, something isn't correct.

If I enter 8/11/08 (Tomorrow which is Saturday) - The MsgBox says ...

The Booking Date 08/11/2008 Falls on a Sunday

If I enter 9/11/08 (Day after tomorrow which is Sunday) - The MsgBox says ...

The Booking Date 09/11/2008 Falls on a Monday

I've tried playing about with the code. Tried to set the FirstDayOfTheWeek = vbSunday. But the MsgBox was still wrong. Even though the code is correctly detecting Saturdays and Sundays.

Any ideas? Thank you.

I think I would use the BeforeUpdate event on the control, that way the user can respond and your code can react accordingly (ie: Cancel the updating of the control and undo the value)

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
    If Weekday(Me.txtDateOfBooking, vbSaturday) <= 2 Then
 
        Cancel = (MsgBox("The Booking Date is on a " & _
                         Format(Me.txtDateOfBooking, "dddd") & vbCrLf & vbCrLf & _
                         "Do you want to keep this date?" _
                         , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
End Sub

Hope that helps!

Thank you I will try this aswell.

 

[datAdrenaline]

Weekday() and WeekdayName() can be effected by system settings that define which day you consider to be the first day of the week. So, in reading the help, you will see that if you DO NOT specify the first day of the week when you call the function, the first day of the week will be the day defined by the system. So ... I try to make it a habit to tell the function what day of the week I call the first day.

Weekday(#11/07/2008#, vbMonday) = 5
Weekday(#11/07/2008#, vbSunday) = 6

The WeekdayName() function accepts an integer as its first argument which is supposed to be the day number, but again the WeekdayName() function needs to know what day of the week is the first day so it can properly evaluate what day of the week it is ... so its possible to really get your brain in a twist ...

WeekDayName(Weekday(#11/07/2008#, vbMonday),,vbMonday) = "Friday"
WeekDayName(Weekday(#11/07/2008#, vbMonday),,vbSunday) = "Thursday"
WeekDayName(Weekday(#11/07/2008#, vbSunday),,vbSunday) = "Friday"

So ... when I want the text of the weekday name, I use the Format() function, which does not care what day you call the first day of the week.

Format(#11/07/2008#,"dddd") = "Friday"

....

Along with all this, do remember that the VBA constants vbSunday through vbSaturday are ALWAYS 1 through 7 respectively ... so a test like this:

Weekday(#11/07/2008#, vbSaturday) = vbSaturday

Is actually not such a good thing because if you evaluate the Weekday() using Saturday as day number 1, then on Saturday, the function will return a 1, which you are then comparing to a 7 ... so thats a no good situation! ...

....

Hope this helps!

{note: all dates in this post are US format of mm/dd/yyyy}

 

[Singh400]

I think I would use the BeforeUpdate event on the control, that way the user can respond and your code can react accordingly (ie: Cancel the updating of the control and undo the value)

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
    If Weekday(Me.txtDateOfBooking, vbSaturday) <= 2 Then
 
        Cancel = (MsgBox("The Booking Date is on a " & _
                         Format(Me.txtDateOfBooking, "dddd") & vbCrLf & vbCrLf & _
                         "Do you want to keep this date?" _
                         , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
End Sub

Hope that helps!

Just tried this piece of code and it works perfectly Thank you very much. I will now attempt to understand how it does what it does

Still not sure why missinglinq's code didn't work as expected. It detected Saturdays and Sundays correctly, just that the MsgBox was a bit drunk. Gonna have a play with that.

Weekday() and WeekdayName() can be effected by system settings that define which day you consider to be the first day of the week. So, in reading the help, you will see that if you DO NOT specify the first day of the week when you call the function, the first day of the week will be the day defined by the system. So ... I try to make it a habit to tell the function what day of the week I call the first day.

Weekday(#11/07/2008#, vbMonday) = 5
Weekday(#11/07/2008#, vbSunday) = 6

The WeekdayName() function accepts an integer as its first argument which is supposed to be the day number, but again the WeekdayName() function needs to know what day of the week is the first day so it can properly evaluate what day of the week it is ... so its possible to really get your brain in a twist ...

WeekDayName(Weekday(#11/07/2008#, vbMonday),,vbMonday) = "Friday"
WeekDayName(Weekday(#11/07/2008#, vbMonday),,vbSunday) = "Thursday"
WeekDayName(Weekday(#11/07/2008#, vbSunday),,vbSunday) = "Friday"

So ... when I want the text of the weekday name, I use the Format() function, which does not care what day you call the first day of the week.

Format(#11/07/2008#,"dddd") = "Friday"

....

Along with all this, do remember that the VBA constants vbSunday through vbSaturday are ALWAYS 1 through 7 respectively ... so a test like this:

Weekday(#11/07/2008#, vbSaturday) = vbSaturday

Is actually not such a good thing because if you evaluate the Weekday() using Saturday as day number 1, then on Saturday, the function will return a 1, which you are then comparing to a 7 ... so thats a no good situation! ...

....

Hope this helps!

{note: all dates in this post are US format of mm/dd/yyyy}

WOW, thats alot of information to asborb. I will attempt to understand it.

 

[datAdrenaline]

>> Thank you very much <<

You are quite welcome! ... We were all glad to help out! ... And if you need further assistance let us know!

 

[Singh400]

>> Thank you very much <<

You are quite welcome! ... We were all glad to help out! ... And if you need further assistance let us know!

I need some more help. I want to add a bit more functionality to this. So as well as having the DB pickup dates entered falling on Saturday and Sunday. I want the DB to alert me if the data entered takes places in the past.

Eg:

Today's date: 01-12-08

And if I enter 11-11-08 in txtDateOfBooking, I want the system/db to alert me to this. Ideally something like:

The data entered takes place in the past. Do you want to keep this date?

Yes / No

Thanks for any help.

 

[pbaldy]

What part are you having trouble with? The test would be:

If Me.txtDateOfBooking < Date() Then

 

[Singh400]

What part are you having trouble with? The test would be:

If Me.txtDateOfBooking < Date() Then

The MsgBox bit, I've tried taking datAdrenaline's MsgBox code and adapting it, but it's not having it. I'm trying different varations of:

If Me.txtDateOfBooking < Date Then
    Cancel = ((MsgBox("The Booking Date takes places in the past") & vbCrLf & vbCrLf & _
 "Do you want to keep this date?" _
 , vbYesNo + vbQuestion) = vbNo)
 If Cancel Then Me.txtDateOfBooking.Undo
EndIf

Does it matter that this code is in the same place [Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)] as the code to check if date falls on Sat or Sun?

 

[pbaldy]

What does "it's not having it" mean? You get an error, unexpected result...?

That code can be in the same procedure, but it would have to be incorporated according to the business logic. IOW, you now have 2 tests. You need to determine which happens first, and what happens if the first test is met or failed. Does it continue to the second test or bail out?

 

[Singh400]

What does "it's not having it" mean? You get an error, unexpected result...?

That code can be in the same procedure, but it would have to be incorporated according to the business logic. IOW, you now have 2 tests. You need to determine which happens first, and what happens if the first test is met or failed. Does it continue to the second test or bail out?

Yikes, didn't even think about the logic of the tests. I'm not great at VBA, I can get by, but thats about it.

"it's not having it" means it's erroring out. Tells me it expected an ")". I guess my syntax is wrong somewhere, but I can't see where. Or I've totally fucked up the MsgBox function. To avoid the logical tests, is it alright if I dump this code in AfterUpdate?

 

[pbaldy]

I don't think the logic changes either way, and the before update event is the place to do validation, since it can be canceled. Try changing this line to:

Cancel = (MsgBox("The Booking Date takes places in the past" & vbCrLf & vbCrLf & _

 

[gemma-the-husky]

go back a bit ...

how can the first day of week affect the weekday

it affects the weekno, but not the day

-
if weekday(anydate)=vbsaturday then
etc

 

[Singh400]

I don't think the logic changes either way, and the before update event is the place to do validation, since it can be canceled. Try changing this line to:

Cancel = (MsgBox("The Booking Date takes places in the past" & vbCrLf & vbCrLf & _

Thank you, that works perfectly. I've put both in the same event. With no unwanted effects...

Private Sub txtDateOfBooking_BeforeUpdate(Cancel As Integer)
    If Weekday(Me.txtDateOfBooking, vbSaturday) <= 2 Then
 
        Cancel = (MsgBox("The Booking Date is on a " & _
                         Format(Me.txtDateOfBooking, "dddd") & vbCrLf & vbCrLf & _
                         "Do you want to keep this date?" _
                         , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
 
    If Me.txtDateOfBooking < Date Then
 
        Cancel = (MsgBox("The Booking Date takes places in the past" & vbCrLf & vbCrLf & _
                        "Do you want to keep this date?" _
                        , vbYesNo + vbQuestion) = vbNo)
 
        If Cancel Then Me.txtDateOfBooking.Undo
 
    End If
End Sub

go back a bit ...

how can the first day of week affect the weekday

it affects the weekno, but not the day

-

if weekday(anydate)=vbsaturday then

etc

Not sure what you mean Gemma?