Merry Christmas !!
I think I must have had too much Christmas cheer as I can't get the syntax correct for this SQL statement I am modifying.

I want the variable (Me.product_choice) from the form that kicked off the code to be read in the SQL but I don't seem to be able to crack it.

StrSQL = "INSERT INTO [" & strTxtFile & " Data Label Numbers] IN 'd:\Database\Product Serial No Records.mdb' " & _
"SELECT " & strTbl & "." & strRec & "customer AS Customer, " & strTbl & "." & strRec & "product_type AS Product," & strTbl & "." & strRec & "serialno AS SerialNo, Product.product_friendlyname AS Friendly, Product.product_weight As Weight, Product.product_code AS Code " & _
"FROM " & strTbl & " LEFT JOIN Product ON " & strTbl & "." & strRec & "product_type = Product.product_name " & _
"WHERE (((" & strTbl & "." & strRec & "date_datalable) Is Null) AND ((" & strTbl & "." & strRec & "product_type) = " & "' Me.product_choice '" & ")) " & _
"ORDER BY " & strTbl & "." & strRec & "serialno;"

The statement worked fine before when it was had hard coded in, but I now want the flexiblility of the varoiable it doesn't work !!

Cheers

I think:
& strRec & "product_type) = " & "' Me.product_choice '" & ")) " &
Should be more like:
& strRec & "product_type) = " & Chr(34) & Me.product_choice & Chr(34) & ")) " &

Where Chr(34) = "

the single quotes need to be inside the string....

) = '" & Me.product_choice & "')) "

Thanks both of you.

I went for your solution scalextrix and it worked fine.

I am not quite sure why yours worked and mine didn't, they both try to put a quote around the variable when it is 'read' as StrSQL, however the proof is in the result !!

Thanks

The reason your code did not work and scalextrix
code did work is because the data you generated in Me.product_choice

is string data and string data must be encapsulated in quotes when used in a query on the fly.


Numeric data does not require the quotes or any other special character.

For future reference:

DATE data must be encapsulated in # in order for the data to be used in a query on the fly.


… #“ & Me.CurrentDate & “# …


Cheers

Richard

Thanks, but I knew I wanted a string format, ie inverted commas around it. and that is what I thought I had with: & "' Me.product_choice '" &
The debug print of the SQL shows me scalextrix solution is correct and mine isn't, but I don't see why?

Cheers