dlookup

[mikeba1]

Having a problem with above.
I am developing a diary system that arranges appointments.
I need to identify if a particular set of dates have a bank holiday within them.
I tried the attached code but it works fine on the second date which is a bank holiday, but fails on the next date which is also a bank holiday

Private Sub Command6_Click()
' loop through a set of dates to determine if they are bank holidays
Dim dloop As Date
Dim k, sqldate, bh
dloop = "14/07/14"
For k = 1 To 6
sqldate = Format$([dloop], "\#dd\/mm\/yyyy\#")
MsgBox (dloop)
bh = DLookup("[bhdate]", "bankholidays", "[bhdate] = " & sqldate & "")
If IsNull(bh) Then
MsgBox ("date not found on bank holidays")
Else
MsgBox ("date FOUND on bank holidays")
End If
dloop = dloop + (6 * 7) ' update in 6 weeks time
Next k
End Sub


Table bankholidays
bhdate Date/Time primary key
bhname Text


data on table bankholidays
bhdate bhname
25/08/14 Summer Bank Holiday
06/10/14 test
25/12/14 Christmas

 

[PHV]

Replace this:
sqldate = Format$([dloop], "\#dd\/mm\/yyyy\#")
with this:
sqldate = Format$(dloop, "\#yyyy-mm-dd\#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mikeba1]

Thanks PH, works fine.
I get confused as the correct format to use at times.
Grey cells falling out the back of the head.

Thanks once again

 

[missinglinq]

Allen Browne, who lives 'down under,' and hence uses non-US date formats, has an excellent article on the subject that is worth bookmarking:

International Dates in Access

The Missinglinq

Richmond, Virginia

The Devil's in the Details!