Variables in a SQL statement

[Nelson]

I am trying to insert a variable into a SQL statement without any luck.
Following is the statement where I am attempting the Public Variable
"sngPickTicketNumber", The watch mode verifies the value is accurate, however
the tables fills in a blank field for the "PickTicketNumber". Any ideas?

DoCmd.RunSQL "SELECT tblFulfillmentInventory.LookupKey, sngPickTicketNumber
AS PickTicketNumber, tblFulfillmentInventory.FashionPONumber,
tblFulfillmentInventory.Style, tblFulfillmentInventory.ColorCode,
tblFulfillmentInventory.Size, tblFulfillmentInventory.RackLocation,
tblFulfillmentInventory.BayNumber, tblFulfillmentInventory.CartonNumber,
tblFulfillmentInventory.Qty AS Available, tblFulfillmentInventory.Qty AS
ShipQty INTO tblFulfillmentShipmentDetail_Temp " & _
"FROM tblFulfillmentInventory " & _
"WHERE
(((tblFulfillmentInventory.FashionPONumber)=[Forms]![frmFulfillmentFPOShipmentInput]![FashionPONumber])); "

 

[Stefan Hoffmann]

hi Nelson,

I am trying to insert a variable into a SQL statement without any luck.
Following is the statement where I am attempting the Public Variable
"sngPickTicketNumber", The watch mode verifies the value is accurate, however
the tables fills in a blank field for the "PickTicketNumber". Any ideas?

DoCmd.RunSQL "SELECT tblFulfillmentInventory.LookupKey, sngPickTicketNumber
AS PickTicketNumber, tblFulfillmentInventory.FashionPONumber,
tblFulfillmentInventory.Style, tblFulfillmentInventory.ColorCode,

Try this:

Dim SQL As String

SQL = "SELECT LookupKey, " & _
Str(sngPickTicketNumber) & " AS PickTicketNumber, " & _
"FashionPONumber, Style, ColorCode, " & _
"Size, RackLocation, BayNumber, CartonNumber, " & _
"Qty AS Available, Qty AS ShipQty " & _
"INTO tblFulfillmentShipmentDetail_Temp " & _
"FROM tblFulfillmentInventory " & _
"WHERE FashionPONumber = " & _
Forms!frmFulfillmentFPOShipmentInput![FashionPONumber]
CurrentDb.Execute SQL, dbFailOnError


mfG
--> stefan <--