Help with Determing if Date is a Holiday

[ccflanna]

I have a form where the user enters a Hearing Date. I need to determine if that hearing date is a holiday. I have created a Holidays table with a list of our holidays in it. I wanted to enter an event procedure in the before update field of the hearing date to check the holidays table against the date entered and if it matches to display the message "This is Holiday". It seems so simple but I can't seem to get it right. Can anyone help me?

 

[SimonB1978]

You could use a DLookup with the selected date in the criteria. If it returns Null, the date was not found in the table.

HTH,

Simon B.

 

[ccflanna]

I was trying this but not working...Can you tell me what is wrong with this? I put it in the before update event procedure. Thank you so much!!


ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& DivHearingDate & "#")) Then MsgBox "This is a Holiday"
End If

End Function

 

[boblarson]

Don't use a DLookup - use a DCount instead:

ElseIf DCount("*", "Holidays", "[HoliDate]=#" & Me.DivHearingDate & "#") > 0 Then
   Msgbox "This is a Holiday"
   Cancel = True
End If

Also, you might need to use formatting to format the date into U.S. date format if you aren't using it like that. See here for more about that:
http://allenbrowne.com/ser-36.html

 

[bob fitz]

Hi Bob Larson

Can you please tell us why DCount would be better than DLookup.

 

[ccflanna]

Thank you so much. I'm almost there I think. I'm getting Compile Error: Else without If when I copy and paste that code in. I'm using Access 2007. Does that matter?

 

[bob fitz]

You may like to try:

If DCount("*", "Holidays", "[HoliDate]=#" & Me.DivHearingDate & "#") > 0 Then
   Msgbox "This is a Holiday"
   Cancel = True
End If

 

[ccflanna]

Thank you so very much! That worked perfectly!!!

 

[boblarson]

Hi Bob Larson

Can you please tell us why DCount would be better than DLookup.

DCount doesn't need to take into account Nulls and it also, from what I understand, is actually faster than returning a DLookup.

 

[bob fitz]

Hi Bob Larson

Thanks for the detail and clarification.

 

[boblarson]

Oh, and I had only included the ElseIf instead of IF because that was in your original code and I assumed you had another If going on there.