Having trouble with DLookUp in A97...

I have tried using DLookUp in this manner...

If DLookUp("[ClientID]","tblClients","[HPhone]='2021234567'") Then
MsgBox "Found it!"
End If

I am wondering if that is a misuse of the DLookUp command?

Type mismatch errors keep popping up. Take a look at this
I typed in the immediate window...

?DLookup("[VehicleMake]", "tblVehicleNames",
"[VehicleMake]=GetCurrentVehicleMake()")
Acura

Acura was returned, as expected.

But when I run the following code in a form module, I
get a type mismatch error running the If DLookUp line...

CurrentVehicleMake = Me!VehicleMake
If DLookup("[VehicleMake]", "tblVehicleNames",
"[VehicleMake]=GetCurrentVehicleMake()") Then
Exit Sub
Else
MySQL = "INSERT INTO tblVehicleNames (VehicleMake) SELECT
GetCurrentVehicleMake() AS MyNewMake;"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
End If

Is it a mistaken belief that if something other than Null is returned
by by the DLookUp function in an If - DLookUp - Then clause
that the If conditional is presumed True and the contents inside the
If - End If wrapper are processed? Have I somehow bastardized the
whole concept, or did this use to work?

re: Having trouble with DLookUp in A97...

Is it because the DLookUp is returning a string???
A string cannot be evaluated as True or False.
That may be the problem. Here is an example of
a scenario that works - without a hitch...

? dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")
116

if dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2") then
msgbox "Hello"

Both of the above lines in the immediate window work as expected.
Of course, 116 evaluates to True a heck of a lot more reliably than
does "Acura". If you agree this is the root of my problem, pls let me
know you concur. Am tired of struggling with this.

re: Having trouble with DLookUp in A97...

"MLH" <CRCI@NorthState.net> wrote in message
news:09s9a1577h6viqno13bbtmf1jubfipqh36@4ax.com...[color=blue]
> Is it because the DLookUp is returning a string???
> A string cannot be evaluated as True or False.
> That may be the problem. Here is an example of
> a scenario that works - without a hitch...
>
> ? dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")
> 116
>
> if dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2") then
> msgbox "Hello"
>
> Both of the above lines in the immediate window work as expected.
> Of course, 116 evaluates to True a heck of a lot more reliably than
> does "Acura". If you agree this is the root of my problem, pls let me
> know you concur. Am tired of struggling with this.[/color]

Why not just test for Null?

If Not IsNull(dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")) Then
MsgBox "Hello"

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Having trouble with DLookUp in A97...

Believe me, I will from now on. Its much clearer.
Thx for the hint.

xxxxxxxxxxxxxxxxxxxxxxxxxxx
[color=blue]
>
>Why not just test for Null?
>
>If Not IsNull(dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")) Then
> MsgBox "Hello"[/color]

re: Having trouble with DLookUp in A97...

Rick Brandt wrote:[color=blue]
> "MLH" <CRCI@NorthState.net> wrote in message
> news:09s9a1577h6viqno13bbtmf1jubfipqh36@4ax.com...
>[color=green]
>>Is it because the DLookUp is returning a string???
>>A string cannot be evaluated as True or False.
>>That may be the problem. Here is an example of
>>a scenario that works - without a hitch...
>>
>>? dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")
>>116
>>
>>if dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2") then
>>msgbox "Hello"
>>
>>Both of the above lines in the immediate window work as expected.
>>Of course, 116 evaluates to True a heck of a lot more reliably than
>>does "Acura". If you agree this is the root of my problem, pls let me
>>know you concur. Am tired of struggling with this.[/color]
>
>
> Why not just test for Null?
>
> If Not IsNull(dlookup("[vehiclejobid]","tblvehiclejobs","[ownerid]=2")) Then
> MsgBox "Hello"
>[/color]

Or use DCount() if checking for existence.

--
[OO=00=OO]