Determine if a date is a Holiday or not 2

[bartus991]

I'm creating a public function which determines if the selected date is a holiday or else which day it is.

But It don't works correctly. I have made a table Holidays, with the fields HolidayName, Startdate, End Date:

Public Function DagNumber(SelectedDate As Date)

'The following values have to be returned:
'Sunday    - 1
'Monday    - 2
'Tuesday   - 3
'Wednesday - 4
'Thursday  - 5
'Friday    - 6
'Saturday  - 7
'Holiday   - 8
  
  If Not Nz(DayNumber = DLookup("ID", "Holidays", "#" & SelectedDate & "# BETWEEN [StartDate] AND [EndDate] "), 0) Then
  DayNumber = 8
  Else
  DayNumber is Weekday(SelectedDate)

End Function

Somehow this will return always the number 8, and haven't figured out where I went wrong.

Please help to get this work, correctly

 

[bartus991]

I have altered the code now as follows:

Public Function DayNumber(SelectedDate As Date)
  
  If Nz(DLookup("ID", "Holidays", "#" & SelectedDate & "# BETWEEN [StartDate] AND [EndDate] "), 0) Then
  DayNumber = 8
  Else
  DayNumber = Weekday(Datum)
  End If

End Function

I works almost. In my table Holiday I have set the following date range:

ID         HolidayName        StartDate      EndDate
 1         Test               01-04-2010     30-04-2010
 2         Test               01-06-2010     30-06-2010

When I set the SelectedDate to a date from 01-04-2010 till 12-04-2010 or from 01-06-2010 till 12-06-2010 it doesn''t work and returns me the weekday value. When I select a date from 13-04-2010 till 30-04-2010 or from 13-06-2010 till 30-06-2010 it gives me the correct value of 8 (Holiday).

Hope someone can help me what goes wrong.

 

[Golom]

I assume that you have a few typos in that

- The function name is "DagNumber" but you are setting "DayNumber"

- "DayNumber is Weekday(SelectedDate)" should be
"DayNumber = Weekday(SelectedDate)"

- You're missing an "End If"

The problem is likely

If Not Nz(DayNumber = DLookup("ID", "Holidays", "#" & SelectedDate & "# BETWEEN [StartDate] AND [EndDate] "), 0) Then

I suspect that [red]DayNumber = DLookup ...[/red] will be treated as a logical comparison between "DayNumber" and the result of "DLookup ..." and not as a variable assignment as you intend. As such it will always cause the If to evaluate to FALSE and return "8".

Should it be

If IsNull(DLookup("ID", "Holidays", "#" & SelectedDate & "# BETWEEN [StartDate] AND [EndDate] ")) Then

?

 

[Golom]

When I set the SelectedDate to a date from 01-04-2010 till 12-04-2010 or from 01-06-2010 till 12-06-2010 it doesn''t work and returns me the weekday value. When I select a date from 13-04-2010 till 30-04-2010 or from 13-06-2010 till 30-06-2010 it gives me the correct value of 8 (Holiday).

You are apparently using a mm-dd-yyyy date convention. Access, being an American invention, defaults to dd-mm-yyyy when the date being read is ambiguous (i.e. day <= 12). For example 05-07-2010 could be May 7 or July 5. To get around that you need to use an unambiguous format like yyyy-mm-dd (for example 2010-05-07 for May 7, 2010)

 

[bartus991]

Thanks for your reply, you are correct there where some typos in it. But I just figured out what went wrong.

Visual basic uses the Month - Day - Year format. If I use this in my field as SelectedDay it works perfectly. But We work with the Day - Month - Year format. So when I set the Date to 1 April, the SelectedDate is 1-4-2010, but visual Basic reads it as 4 january 2010.

Maybe you have a suggetions how I can get VB to read the date correctly.

 

[Golom]

I do ... read the post above your last one.

 

[SkipVought]

Real DATE VALUES as NUMBERS, not mm-dd-yyyy or dd-mm-yyyy. Like right now in North Texas the Date/Time values is 40265.40342. That NUMBER can be FORMATED (formatting changes NOTHING with respect to the underlying value) all kinds of different ways, in accordance with how we want to VIEW the data.

When we use mm-dd-yyyy or dd-mm-yyyy in code, it must be CONVERTED to a Date Serial value. The problem with either mm-dd-yyyy or dd-mm-yyyy, is that they are AMBIGUOUS. Good coders never assume a regional convention. Rather, use THE UNAMBIGUOUS structure, yyyy-mm-dd, as the text that will be converted.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bartus991]

Thanks!

Just added : DateSelection = Format(SelectedDate, "yyyy-mm-dd") and now It works great!