SQL Syntax, incorporating a variable

[Peter Bellamy]

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

 

[scalextric59]

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

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

 

[Peter Bellamy]

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

 

[rapsr59]

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.

[COLOR=black][SIZE=3][FONT=Times New Roman]… #“ & Me.CurrentDate & “# …[/FONT][/SIZE][/COLOR]

Cheers

Richard

 

[Peter Bellamy]

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