Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in SQL
Hi, How do you put a variable in an SQL statement in Excel?
There's a form where the user selects project number from a combo box. I want to take the project number the user selected and use in the SQL below. I keep getting either syntax errors or Expected: named parameter. Please help! cboProjectNumberID = intDSRProjectNumber adoRecordset.Open _ Source:="SELECT tblDSREquipment.DSREquipmentID, tblDSREquipment.BWProjectNumberID, tblDSREquipment.VendID, tblDSREquipment.COAID, tblDSREquipment.EquipmentShipDate, tblDSREquipment.POReleaseDate, tblDSREquipment.DSRCreateDate, tblDSREquipment.DSRDocumentNumber, tblDSREquipment.DSRDocumentRevision, tblDSREquipment.SystemPartPolicy, tblDSREquipment.ReleaseIndicator, tblDSREquipment.EText, tblDSREquipment.LifecycleState, tblDSREquipment.UOM, tblDSREquipment.PartBLSCreated, tblProjectData.BWProjectNumber, tblProjectData.BWProjectName " & _ "FROM tblDSREquipment INNER JOIN tblProjectData ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID " & _ "WHERE (((tblDSREquipment.BWProjectNumberID)= (' & intDSRProjectNumber & '))", |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in SQL
Michelle,
It looks like you are trying to assign the value of "intDSRProjectNumber" to the combo box with "cboProjectNumberID = intDSRProjectNumber". Flip it to assign the selected value in the combo box to the variable: intDSRProjectNumber = cboProjectNumberID OR intDSRProjectNumber = cboProjectNumberID.Value Or you could elemenate the variable by doing something like this: ..."WHERE (((tblDSREquipment.BWProjectNumberID)= (' & cboProjectNumberID.Value & '))", HTH, Conan "Michelle" wrote in message ... Hi, How do you put a variable in an SQL statement in Excel? There's a form where the user selects project number from a combo box. I want to take the project number the user selected and use in the SQL below. I keep getting either syntax errors or Expected: named parameter. Please help! cboProjectNumberID = intDSRProjectNumber adoRecordset.Open _ Source:="SELECT tblDSREquipment.DSREquipmentID, tblDSREquipment.BWProjectNumberID, tblDSREquipment.VendID, tblDSREquipment.COAID, tblDSREquipment.EquipmentShipDate, tblDSREquipment.POReleaseDate, tblDSREquipment.DSRCreateDate, tblDSREquipment.DSRDocumentNumber, tblDSREquipment.DSRDocumentRevision, tblDSREquipment.SystemPartPolicy, tblDSREquipment.ReleaseIndicator, tblDSREquipment.EText, tblDSREquipment.LifecycleState, tblDSREquipment.UOM, tblDSREquipment.PartBLSCreated, tblProjectData.BWProjectNumber, tblProjectData.BWProjectName " & _ "FROM tblDSREquipment INNER JOIN tblProjectData ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID " & _ "WHERE (((tblDSREquipment.BWProjectNumberID)= (' & intDSRProjectNumber & '))", |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in SQL
I think your parenthesis and single quotes were wrong. This example may help
you understand string better. the SQL is a string. SelectStr = "SELECT tblDSREquipment.DSREquipmentID, " & _ "tblDSREquipment.BWProjectNumberID, " & _ "tblDSREquipment.VendID, " & _ "tblDSREquipment.COAID, " & _ "tblDSREquipment.EquipmentShipDate, " & _ "tblDSREquipment.POReleaseDate, " & _ "tblDSREquipment.DSRCreateDate, " & _ "tblDSREquipment.DSRDocumentNumber, " & _ "tblDSREquipment.DSRDocumentRevision, " & _ "tblDSREquipment.SystemPartPolicy, " & _ "tblDSREquipment.ReleaseIndicator, " & _ "tblDSREquipment.EText, " & _ "tblDSREquipment.LifecycleState, " & _ "tblDSREquipment.UOM, " & _ "tblDSREquipment.PartBLSCreated, " & _ "tblProjectData.BWProjectNumber, " & _ "tblProjectData.BWProjectName " FromStr = "FROM tblDSREquipment INNER JOIN tblProjectData " & _ "ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID" WhereStr = "WHERE (tblDSREquipment.BWProjectNumberID= '" & _ intDSRProjectNumber & "')" adoRecordset.Open _ Source:=SelectStr & Chr(13) & Chr(10) & _ FromStr & Chr(13) & Chr(10) & _ WhereStr "Michelle" wrote: Hi, How do you put a variable in an SQL statement in Excel? There's a form where the user selects project number from a combo box. I want to take the project number the user selected and use in the SQL below. I keep getting either syntax errors or Expected: named parameter. Please help! cboProjectNumberID = intDSRProjectNumber adoRecordset.Open _ Source:="SELECT tblDSREquipment.DSREquipmentID, tblDSREquipment.BWProjectNumberID, tblDSREquipment.VendID, tblDSREquipment.COAID, tblDSREquipment.EquipmentShipDate, tblDSREquipment.POReleaseDate, tblDSREquipment.DSRCreateDate, tblDSREquipment.DSRDocumentNumber, tblDSREquipment.DSRDocumentRevision, tblDSREquipment.SystemPartPolicy, tblDSREquipment.ReleaseIndicator, tblDSREquipment.EText, tblDSREquipment.LifecycleState, tblDSREquipment.UOM, tblDSREquipment.PartBLSCreated, tblProjectData.BWProjectNumber, tblProjectData.BWProjectName " & _ "FROM tblDSREquipment INNER JOIN tblProjectData ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID " & _ "WHERE (((tblDSREquipment.BWProjectNumberID)= (' & intDSRProjectNumber & '))", |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable in SQL
First off, Conan, made the changes you suggested and I now have a variable
that's not null..Thank you so much! Joel, Changed code as follows but now I'm getting a -2147217900 error "Select statement includes a reserved word or an argument name that is misspelled or missing, or punctuation is incorrect." Its highlighting the adoRecordset.Open statement. Please help! Set adoRecordset = New ADODB.Recordset SelectStr = "SELECT tblDSREquipment.DSREquipmentID, " & _ "tblDSREquipment.BWProjectNumberID, " & _ "tblDSREquipment.VendID, " & _ "tblDSREquipment.COAID, " & _ "tblDSREquipment.EquipmentShipDate, " & _ "tblDSREquipment.POReleaseDate, " & _ "tblDSREquipment.DSRCreateDate, " & _ "tblDSREquipment.DSRDocumentNumber, " & _ "tblDSREquipment.DSRDocumentRevision, " & _ "tblDSREquipment.SystemPartPolicy, " & _ "tblDSREquipment.ReleaseIndicator, " & _ "tblDSREquipment.EText, " & _ "tblDSREquipment.LifecycleState, " & _ "tblDSREquipment.UOM, " & _ "tblDSREquipment.PartBLSCreated, " & _ "tblProjectData.BWProjectNumber, " & _ "tblProjectData.BWProjectName, " FromStr = "FROM tblDSREquipment INNER JOIN tblProjectData " & _ "ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID" WhereStr = "WHERE (tblDSREquipment.BWProjectNumberID= '" & _ intDSRProjectNumber & "')" adoRecordset.Open _ Source:=SelectStr & Chr(13) & Chr(10) & _ FromStr & Chr(13) & Chr(10) & _ WhereStr, _ ActiveConnection:=UsageTracking, _ CursorType:=adOpenStatic, _ LockType:=adLockReadOnly, _ Options:=adCmdText adoRecordset.MoveFirst "joel" wrote: I think your parenthesis and single quotes were wrong. This example may help you understand string better. the SQL is a string. SelectStr = "SELECT tblDSREquipment.DSREquipmentID, " & _ "tblDSREquipment.BWProjectNumberID, " & _ "tblDSREquipment.VendID, " & _ "tblDSREquipment.COAID, " & _ "tblDSREquipment.EquipmentShipDate, " & _ "tblDSREquipment.POReleaseDate, " & _ "tblDSREquipment.DSRCreateDate, " & _ "tblDSREquipment.DSRDocumentNumber, " & _ "tblDSREquipment.DSRDocumentRevision, " & _ "tblDSREquipment.SystemPartPolicy, " & _ "tblDSREquipment.ReleaseIndicator, " & _ "tblDSREquipment.EText, " & _ "tblDSREquipment.LifecycleState, " & _ "tblDSREquipment.UOM, " & _ "tblDSREquipment.PartBLSCreated, " & _ "tblProjectData.BWProjectNumber, " & _ "tblProjectData.BWProjectName " FromStr = "FROM tblDSREquipment INNER JOIN tblProjectData " & _ "ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID" WhereStr = "WHERE (tblDSREquipment.BWProjectNumberID= '" & _ intDSRProjectNumber & "')" adoRecordset.Open _ Source:=SelectStr & Chr(13) & Chr(10) & _ FromStr & Chr(13) & Chr(10) & _ WhereStr "Michelle" wrote: Hi, How do you put a variable in an SQL statement in Excel? There's a form where the user selects project number from a combo box. I want to take the project number the user selected and use in the SQL below. I keep getting either syntax errors or Expected: named parameter. Please help! cboProjectNumberID = intDSRProjectNumber adoRecordset.Open _ Source:="SELECT tblDSREquipment.DSREquipmentID, tblDSREquipment.BWProjectNumberID, tblDSREquipment.VendID, tblDSREquipment.COAID, tblDSREquipment.EquipmentShipDate, tblDSREquipment.POReleaseDate, tblDSREquipment.DSRCreateDate, tblDSREquipment.DSRDocumentNumber, tblDSREquipment.DSRDocumentRevision, tblDSREquipment.SystemPartPolicy, tblDSREquipment.ReleaseIndicator, tblDSREquipment.EText, tblDSREquipment.LifecycleState, tblDSREquipment.UOM, tblDSREquipment.PartBLSCreated, tblProjectData.BWProjectNumber, tblProjectData.BWProjectName " & _ "FROM tblDSREquipment INNER JOIN tblProjectData ON tblDSREquipment.BWProjectNumberID = tblProjectData.BWProjectNumberID " & _ "WHERE (((tblDSREquipment.BWProjectNumberID)= (' & intDSRProjectNumber & '))", |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a Variable to an Expression that Includes a Variable andVBA Property | Excel Programming | |||
Nothing Keyword Destories Objects rather than just resetting the variable to an empty variable | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |