SQL with Dlookup

[djlysuc]

I am struglling with a query, although I may be going about it in the wrong way, so any help or points in the right direction is appreciated.

I have an import process that creates a table with a text field like below:

raw
12345A
15432B
61451C

I have another table that has the conversion values for the final alpha character in the raw field, like below:

zoned numeric
A 1
B 2
C 3

My first query creates a new numeric double field in the table and then my second query tries to populate it as follows:

strSQL = "UPDATE [import file] SET [import file].[converted] = Left([import file].[raw],5) & Dlookup('[numeric]', '[tbl_conversion]', '[zoned] = ' & Right([import file].[raw],1) & ');"
DoCmd.RunSQL strSQL

Access returns "unknown error" when you run this.

 

[Ancalima]

Your problem, it would seem, would be the structuring of your string around the DLookup function. Simply using single quotes wont work here since you need to create strings for the dlookup within your overall string for the update query. Therefore you need to forcfully inject double quotes into your string using the Chr(34) function. For the criteria portion of the dlookup it is important you preserve some of the code portion of the string for accurate interpretation of the function. Try using the following code and see if it works for you.

DoCmd.RunSQL "UPDATE [import file] SET [import file].[converted] = Left([import file].[raw],5) & Dlookup(" & Chr(34) & "[numeric]" & Chr(34) & ", " & Chr(34) & "[tbl_conversion]" & Chr(34) & ", " & Chr(34) & "[zoned] = " & Chr(34) & " & Chr(34) & Right([import file].[raw],1) & Chr(34)" & ");"

 

[djlysuc]

Thanks Ancalima.