Dlookup with different data types as criteria

[Nathan Wolfe]

In my form when a user adds stats I want to use Dlookup to see if the user is
trying to enter a duplicate. Here's what I have already:

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'"))
Then
(Some kind of warning)

This piece of code works fine, but I need to use more than location; I need
to use a date and time as well.

This piece of code also works:
If Not IsNull(DLookup("[RegularStopID]", "tblStops", "[Datestop] = #" &
Me!cboDate & "#"))
Then
(Some kind of warning)

But this doesn't work:
If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'" And "[Datestop] = #" & Me!cboDate & "#"))
Then
(Some kind of warning)

I get a "Type Mismatch" error. Is it not possible to do what I'm trying to
do, or is my syntax wrong? I'd appreciate any hlep.

 

[fredg]

In my form when a user adds stats I want to use Dlookup to see if the user is
trying to enter a duplicate. Here's what I have already:

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'"))
Then
(Some kind of warning)

This piece of code works fine, but I need to use more than location; I need
to use a date and time as well.

This piece of code also works:
If Not IsNull(DLookup("[RegularStopID]", "tblStops", "[Datestop] = #" &
Me!cboDate & "#"))
Then
(Some kind of warning)

But this doesn't work:
If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'" And "[Datestop] = #" & Me!cboDate & "#"))
Then
(Some kind of warning)

I get a "Type Mismatch" error. Is it not possible to do what I'm trying to
do, or is my syntax wrong? I'd appreciate any hlep.

From your code, I gather that [RegularStopID] is a Text datatype
field. If so, you have an extra " just before the word And, so
therefore the word And is not in the criteria string.
V
& Me!cboLocation & "'" And


Try it this way.

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "' And [Datestop] = #" & Me!cboDate & "#"))
Then

 

[Douglas J. Steele]

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "' And [Datestop] = #" & Me!cboDate & "#"))

 

[Ofer Cohen]

Try

If DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "' And [Datestop] = #" &
Me!cboDate & "#")

 

[George Nicholson]

1) you have the wrong mix of quotes & ampersands around the AND. The word
AND is not being included in the Dlookup Criteria argument you are
constructing, and it needs to be part of the string. As it stand now you're
telling it something like:
"LocationString" And "DateString"
That's not a string, which is what dlookup expects. Result: type mismatch

You want: "LocationString And DateString"

2) I would add parenthesis to separate the 2 clauses you want to be
evaluated with AND, but that may just be a personal bias towards making
things as explicit as possible for Jet.

"([Location] = '" & Me!cboLocation & "') And ([Datestop] = #" & Me!cboDate &
"#")))

the end result you are aiming for is:
"([Location] = 'SomeString') And ([Datestop] = #SomeDate#")))

You could also 1) create a strCriteria variable, 2) assign your construction
to strCriteria and 3) use strCriteria as the 3rd dlookup argument. One
advantage to this is you can then 4) easily add a breakpoint on the Dlookup
line and type...
? strCriteria
....in the Immediate window when you hit the breakpoint to see *exactly* what
you are passing to Dlookup and whether it makes sense. Most SQL string
problems become readily apparent.

HTH,

 

[Nathan Wolfe]

Thanks to all who answered. I looked at code too long and went cross-eyed.

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "' And [Datestop] = #" & Me!cboDate & "#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In my form when a user adds stats I want to use Dlookup to see if the user
is
trying to enter a duplicate. Here's what I have already:

If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'"))
Then
(Some kind of warning)

This piece of code works fine, but I need to use more than location; I
need
to use a date and time as well.

This piece of code also works:
If Not IsNull(DLookup("[RegularStopID]", "tblStops", "[Datestop] = #" &
Me!cboDate & "#"))
Then
(Some kind of warning)

But this doesn't work:
If (DLookup("[RegularStopID]", "[tblStops]", "[Location] = '" &
Me!cboLocation & "'" And "[Datestop] = #" & Me!cboDate & "#"))
Then
(Some kind of warning)

I get a "Type Mismatch" error. Is it not possible to do what I'm trying to
do, or is my syntax wrong? I'd appreciate any hlep.