Access 2000 date madness

[helena]

Hello

I hope this is an easy one and that I'm just having a momentary relapse of brain cells...

I have the following very simple piece of code:

Public Function BankHol(BHDate As Date) As Boolean
Dim X As Variant

X = DLookup("[BH_Date]", "[T_BankHolidays]", "[BH_Date] = " & "#" & BHDate & "#")

If IsNull(X) Then
BankHol = False
Else
BankHol = True
End If


End Function

The function "should" assess whether a date passed to it is stored in a bank holiday dates table. If it is then it returns true. If not false. The problem is it returns false even if the date IS in the bank holiday dates table. I have tried several ways of putting the criteria in the third part of the DLookup both with and without the #'s. The function actually works in the immeadiate window if I type in the #'s around the date but doesn't if I leave them out. The field in the bank holiday dates table is Date data type and the input mask for data entry in the form is dd/mm/yyyy. The regional settings are set to Short date style dd/mm/yyyy. I have also tried converting the BHDate variable to date by using CDate conversion function but it still doesn't work!! Please help - am I going mad?

Thanks in advance.

Helena

 

[Robert Saye]

Hi Helena,

The problem has to be that you are not passing a date in. Your Dlookup statement works just fine. I changed it to a procedure and ran it with some fake data and it passes.
Public Sub Testing()
Dim BHDate As Date
Dim X As Variant
Dim BankHol As Boolean
Dim str As String
BHDate = #12/24/2001#
str = ("#" & BHDate & "#")
X = DLookup("[field1]", "[table1]", "[field1] = " & str)

If IsNull(X) Then
BankHol = False
Else
BankHol = True
End If
Debug.Print BankHol
Debug.Print BHDate


End Sub

So what ever you are using to pass the BHDate in, i.e. pop up form or a field from a form, ensure that it is a date type. A good test is to step through each line of code, and check the value of the date beforehand.


HTH
Robert

 

[helena]

Thanks for that Robert, I will check it out.

Helena

 

[Jon Holmes]

Helena,

The problem lies not with your code but with the format of the date. Living outside the US presents numerous problems for programmers when it comes to dates. Try this line instead;
X = DLookup("BH_Date", "T_BankHolidays", "BH_Date = #" & format(BHDate, "mm/dd/yy") & "#")
When working in code, programmers whose regional settings are non-US, must explicitly convert all dates to US format.

Jon

 

[A real begineer]

I thought as you do John. However, provided the Windows regional setting is formatted for the correct region, and both dates are in the same format, it does not seem to present a problem in the DLookup function, for us Antipodean programmers.

 

[helena]

Thanks Jon - I will now give that a go. Robert - I looked at the date I was passing in - its directly from a date field in a table so I was definitely passing a date to the function.

Interestingly the dates where days and months cannot be confused work, it's just the ones where days and months could be swapped round that don't. I was specifically having trouble with the last bank hol date of 07/05/2001. Hopefully Jon's advice will work.

Many thanks for the help.

Helena

 

[helena]

It now works!