Question on DLookup Syntax

[Yes2man]

I can't seem to get the right syntax to make the expression work correctly
beginning with "[Begin Date]...".

After the user inputs a date (via an input box, to the variable 'varxx',
which is Dim as Date), I want the DLookup to find that date on another table.

varX = IsNull((DLookup("[Begin Date]", "Walk Around Schedule: All Items",
"[Begin Date] = #" & varxx & “#â€)))

I have tried many combinations of # and " but I can't seem to get it right.
I keep getting syntax error. Any help would be appreciated!

Thanks

 

[Allen Browne]

In the end, your varX will be True (the date was found) or False (the
Dlookup() produced Null.) Is that what you intend?

Perhaps you can break this down into several steps, so you can identify
where it is going wrong. This example has MsgBoxes everywhere to help debug
it. It takes these steps:

- accepts the value from the input box into a string

- checks it is a valid date, converts it, and explains how it was
understood.

- builds the criteria string, with the date correctly formatted.
(Do NOT change strcJetDate to your own local settings.)

- performs the lookup, and reports it.

Dim dt as Date
Dim strIn As String
Dim strWhere As String
Dim varResult As Variant
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strIn = Inputbox("What date?")
If IsDate(strIn) Then
dt = CDate(strIn)
MsgBox "Date understood as " & Format(dt, "Medium Date")
strWhere = "[Begin Date] = " & Format(dt, strcJetDate)
varResult = DLookup("[Begin Date]", "Walk Around Schedule: All Items",
strWhere)
MsgBox "Result of lookup is " & varResult
If IsNull(varResult) Then
MsgBox "Not found"
Else
MsgBox "Found"
End If
ElseIf strIn = "" then
MsgBox "Nothing to do"
Else
MsgBox "Not recognised as a date"
End If