str variable in dlookup criteria (1 Viewer)

[Thales750]

This is making me crazy.

Why does this work when the text box is used and not when the variable is used directly?

Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
 
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
 
 
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
 
 
 
End Sub

Text62 returns the correct value
Text64 failes on everyone of the examples

 

[CJ_London]

because you need to use single quotations around the target i.e.

'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = ." & strDBName & "'")

 

[Thales750]

CJ,

There is a typo in the code you pasted.

'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = ." & strDBName & "'") 
 
should read:
 
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = ' " & strDBName & "'")

 

[CJ_London]

sorry, my mistake!

 

[boblarson]

This should have also worked:

Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & Chr(34) & strDBName & Chr(34))

 

[ions]

I also prefer to use Chr(34). It's a good habit to get into because if the string itself contains ' (i.e. Company's Database) you will get a runtime error. With chr(34) you will never get a runtime error so I prefer to use it over "'". It's just a good habit.

 

[The_Doc_Man]

To understand why/how this works, look at the code as though it works like this:

DLookup( "A", "B", "C" ) internally does something very similar to this:

tQRY = "SELECT " & A & " FROM " & B & " WHERE " & C & " ; "

Then it opens a recordset of that query and reads the first returned record, which contains a single field as specified by your "A" argument. That is returned as your answer in whatever format is required. The result of DLookup comes back as a variant but using syntax of "X = variant" silently converts the variant format to the format of X so you never see the difference.

You can see that in order for this process to work, you have to do a string substitution in each case, which requires proper quoting. Consider doing that substitution directly iv VBA vs. using DLookup using the three cases you posted. The case of {"[]" = var} is not going to play well in substitution.

Now, purists might question whether what I posted is EXACTLY what is done inside Access, but I'm just saying it is very SIMILAR to what I showed you. If you think of the domain aggregates in that context, you'll never go wrong.

 

[CJ_London]

I also prefer to use Chr(34).

Don't disagree but I prefer the use of the replace function to replace single quotes with two single quotes - particularly when updating a table or accepting user input.

replace(strDBName,"'","''")