Variable in SQL

[Michelle]

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]

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

 

[joel]

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]

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