Hard Case on Weekday (1 Viewer)

[lightray]

Hi am having a problem with my weekday formula (kindly provided by the forum) thanks However I am using it in a Select Case and it doesn't appear to be working. I have tracked it from a breakpoint and it just bypasses the checking.

My code Is:

    Select Case Me.EndDate
        Case Weekday(Me.EndDate) = 1 Or Weekday(Me.EndDate) = 7
            MsgBox "Last Day of Leave is a " & WeekdayName([Me.EndDate]) & vbCrLf & "Invalid End Date", vbExclamation, "!Warning"
            Cancel = True
            Me.EndDate.Undo
            Exit Sub
    ...other cases ...

It's all plausable? isn't it?

 

[pbaldy]

Try:

Select Case Weekday(Me.EndDate)
  Case 1, 7

 

[lightray]

thanks Pbaldy, too focused on the other cases not to spot that, and it would have taken me a while. have a little trouble with the Msgbox now as I want to display weather it was a 'saturday' or 'sunday'.
from the original code posted I've also tried: WeekdayName((WeekDay([Me.EndDate])) as suggested by other posts but am getting a run time error '2465' can't find the field '|' referred to in your expression.
any thoughts?

 

[pbaldy]

You've got the wrong number of parentheses there, unless it's a typo. I just tested this:

WeekdayName(Weekday([FieldName]))

 

[lightray]

Yeh! it was a typo in the post. Are you able to test it in a MsgBox? I wonder if its a problem there, perhaps pass it in as a variable? I will try ...
lightray

    Select Case Weekday(Me.EndDate)
        Case 1, 7
            [I]txtWeekendDay = WeekdayName(Weekday([Me.EndDate]))[/I]
            MsgBox "Last Day of Leave is a " & txtWeekendDay & vbCrLf & "Invalid End Date", vbExclamation, "!Warning"
            Cancel = True
            Me.EndDate.Undo
            Exit Sub

Tripped up on the Italic line with the sme error message

 

[gemma-the-husky]

just

format (enddat,"dddd") when you display it will give you the DAY NAME

 

[Rich]

Why are you surrounding Me.EndDate with brackets? [Me.EndDate]?

"Last Day of Leave is a " & " " & Format(EndDate, "dddd") & vbCrLf & " " & " Invalid etc"

 

[lightray]

Thanks Teams, I discovered the problem, but seemed to get the wrong answer. Yeh! you're onto it Rich. I tried also Me.[EndDate] but my input test Saturday 25/11/2006, said it was Sunday! not sure what's happening there.

However I have implemented the fabulous gemma's idea (sorry Rich, she got there first) and I'm happy to call it, done, but not dusted. I have 3 different forms that have start dates and end dates and I want to implement the same tests. I dare say I could build this into a module with the other date tests
Is EndDate < StartDate; Is StartDate on a Weekend;

Thanks for your input it's been great
Current solution

   Select Case Weekday(Me.EndDate)
        Case 1, 7
            MsgBox "Last Day of Leave is a " & Format(EndDate, "dddd") & vbCrLf & "Invalid End Date", vbExclamation, "!Warning"
            Cancel = True
            Me.EndDate.Undo
            Exit Sub

 

[gemma-the-husky]

lightray

weekday determines which day of the week (ie 1-7) a date is. I think the default for day1 is Sunday but there is an optional parameter, where you can set a different day1.

Hence

Weekday(Me.EndDate,vbmonday) indicates that day1 should be monday etc.

have a look at the help about this.

 

[Rich]

It's Me.EndDate or Me!EndDate or [EndDate] depending on whether enddate is the name of the field or control