SQL and Variables

[lizray]

I am construction a query and want to include a field as an expression as follows:
IIF(isnull(Place),strPlacename,Place)
where place is a field in a table; but strPlacename is a variable containing the name of a place to be used if Place is Null. SQL has a problem with it. I have tried "'" & strPlacename & "'" but no luck... How can I get this to work ??

 

[PHV]

Do you create the SQL by code ?

strSQL = strSQL & ", Nz(Place,'" & strPlacename & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lizray]

Thanks PH. I havent heard of the Nz function, but tried it and the string " & strPlacename & " appeared in that field, rather then the actual variable stored in strPlacename. I started with Design view in access query and then have been working from the SQL view

 

[PHV]

SQL can't retrieve variables but functions.