Using string variable for Dlookup criteria 1

[inso18]

Hi All.

What's the correct syntax for

Dim strS as String
strS = "Mike"
Msgbox Dlookup (field.name, "tblTable", "field1=" & strS)

This doesn't work, I can't figure out the correct one maybe because understand the rules behind all the " & ' signs when using these stuff, and haven't found any reading source for learning this issue.

 

[Remou]

Strings need single quotes:

[tt]Msgbox Dlookup (field.name, "tblTable", "field1='" & strS & "'")[/tt]

This field.name looks wrong to me. You need the name of a field or function as a string.

 

[PHV]

A safer way:

MsgBox DLookup("[field name]", "tblTable", "field1='" & Replace(strS, "'", "''") & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Thanks a lot.

(the field.name is actually a recordset item.)

Can you please explain the logic behind this syntax? Why there have to be ' ' brackets and & & and " "?

 

[PHV]

is the VBA literal constant delimiter
& is the VBA string concatenation operator
' is the SQL literal constant delimiter

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Ok, and why do they have to be in this specific order in this case?

 

[PHV]

In this case ?
[tt]"field1='" & strS & "'"[/tt]

The goal is to send to the function the following WHERE clause as a literal constant:
[tt]field1='Mike'[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Ahh, I think I understand now.

This is something further on topic, how the correct result should be achieved in a query creteria field, if I it is, for ex:

[ClientID] is a number
IsNull(DCount("CustomerID","Customers","CustomerID="&[CustomerID]))

the criteria here works, but not correctly (When I manualy write a number "CustomerID=1", it does work correctly.

I'll appritiate if you can explain this too.

 

[PHV]

Sorry, I don't see what to explain as you don't say what but not correctly means.
What is [ClientID] in that context ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Oops, it is supposed to be [Customer_Id].

 

[PHV]

Anyway, I don't think the DCount function may return a Null value ...

 

[inso18]

My fault, it does work.
After this post I understand more about how to correctly combine delimiters. Thanks PHV and Remou.

 

[inso18]

Yeah, afterwards I used a ="0" instead of IsNull.