DLookup syntax error?

[mlopes1]

Dim fmName as string
Dim InUseVar as string

fmName = "Test1"

InUseVar = DLookup("[In_Use]", "All_Forms", "[Form_Name] = ' " & fmName & " ' ")

Basically, I just want to take the value of the field In_Use found in table All_Forms and set InUseVar equal to it, when the field Form_Name = fmName.

I get the error revious operation was canceled.

Any clues here? Hopefully this enough info,

Marco

 

[Mile-O]

You are looking for a text string with the value " Test1 " and not "Test1"

Note the spaces on either side of the value; you are putting these in. Wrong!

InUseVar = DLookup("[In_Use]", "All_Forms", "[Form_Name] = """ & fmName & """")

 

[mlopes1]

Hi Pat,

Thanks for that syntax fix, I would have never found it. I entered it exactly as you suggested however still was told "Previous Operation Canceled". Perhaps you have some ideas why this is happening.

I call the function out of an OnClick event...

fmName = "test1"

FmCheck fmName

And then in the function have your code:

Dim InUseVar as String

InUseVar = DLookup("[In_Use]", "All_Forms", "[Form_Name] = ' " & fmName & " ' ")


Anything jump out at you here? Thanks as always for all your help. Much needed!

Marco

 

[dcx693]

Pat? I didn't see a Pat in this thread - (in about 20,000 other threads yes, but not this one. )

 

[mlopes1]

LOL.... sorry about that. I guess its just like 2nd nature now to write that. Thanks for your answer!

 

[Pat Hartman]

Mile's new avitar does look a little like me

 

[Mile-O]

Ex ovo Patia?

 

[mary.h]

Hi Marco,

well, I've got sometimes problems with DLookup, if it the search-string isn't found and so DLookup returns "NULL". So I use

myString = Nz(DLookup("FieldName","Tablename","Field2='" & var & "'"),"empty")

So myString = "empty", if DLookup is Null.

Maybe that helps.

Regards,
Mary H.