ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Variable in SQL (https://www.excelbanter.com/excel-programming/427740-variable-sql.html)

michelle
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
& '))",

Conan Kelly
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
& '))",



joel
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
& '))",

michelle
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
& '))",


All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com