SQL help

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello,
I'm trying to retun a record from a table (tlbWarehouse) based on a variable
(strOrgWarehouseID) in my function. Currently my sql statement looks like
this:

"SELECT * FROM tblWarehouse Where strWarehouseID = strOrgWarehouseID" but it
doesn't return the correct record. It returns the first record in the table.
Can anyone tell me what I'm doing wrong?
Thanks

Jim
 
Your SQL string should PROBABLY read like the following if
strWareHouseID is a string value.

"SELECT * FROM tblWarehouse Where strWarehouseID =""" &
strOrgWarehouseID & """"

That is a guess since you didn't post any of your code.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks, that worked and strWarehouseID is a string value. What's the reason
for having the quotes as opposed to the way I had it? I'm trying to
understand "why" this worked.

Jim
 
You need to concatenate the value of strOrgWareHouseId into the SQL
statement. And string literal values need to be surrounded by quote marks.

The query engine (Jet) has no idea what strOrgWareHouseID is. It would
attempt to treat it as a parameter or a field name. Assuming
strOrgWareHouseID is equal to "A2345", your SQL string would look like.

SELECT * FROM tblWarehouse Where strWarehouseID = strOrgWarehouseID

My proposed would look like
SELECT * FROM tblWarehouse Where strWarehouseID = "A2345"

I'm curious why your version did not throw an error or request you to
supply a value for a parameter named strOrgWarehouseID.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
When I try to add a Where condition, I'm getting the following error:

Syntax error (missing operator) in query expression 'Where strWarehouseID =
"Wagner"

Here is the code I'm currently trying to use.

Thanks for your help.


Public Function Warehouse(strOrgWarehouseID) As String

Dim strCity As String
Dim strWarehouseID As String

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

With rs
.CursorType = adOpenStatic

.Open "SELECT tblWarehouse.strWarehouseID,
tblWarehouse.strDescription, tblWarehouse.strAddress," & _
"tblWarehouse.strCity, tblWarehouse.strState, tblWarehouse.strZip,
First(tblWarehouseContactMethod.strPhoneNumber)" & _
"AS FirstOfstrPhoneNumber FROM tblWarehouse INNER JOIN
tblWarehouseContactMethod ON tblWarehouse.strWarehouseID" & _
"= tblWarehouseContactMethod.strWarehouseID GROUP BY
tblWarehouse.strWarehouseID, tblWarehouse.strDescription," & _
"tblWarehouse.strAddress, tblWarehouse.strCity, tblWarehouse.strState,
tblWarehouse.strZip, Where strWarehouseID =""" & strOrgWarehouseID & """",
CurrentProject.Connection
End With

strCity = rs!strDescription & vbCrLf & vbCrLf & _
rs!strAddress & vbCrLf & vbCrLf & _
rs!strCity & " " & rs!strState & " " & rs!strZip & vbCrLf &
_
Format(rs!FirstOfstrPhoneNumber, "(###) ###-####")

rs.Close

Set rs = Nothing

Warehouse = Format(strCity, ">")

End Function
 
Fix the query string

"SELECT tblWarehouse.strWarehouseID, tblWarehouse.strDescription" & _
", tblWarehouse.strAddress, tblWarehouse.strCity" & _
", tblWarehouse.strState, tblWarehouse.strZip" & _
", First(tblWarehouseContactMethod.strPhoneNumber)" & _
" AS FirstOfstrPhoneNumber" & _
" FROM tblWarehouse INNER JOIN tblWarehouseContactMethod" & _
" ON tblWarehouse.strWarehouseID =" & _
" tblWarehouseContactMethod.strWarehouseID" & _
" WHERE strWarehouseID =""" & strOrgWarehouseID & """" & _
" GROUP BY tblWarehouse.strWarehouseID, tblWarehouse.strDescription" & _
", tblWarehouse.strAddress, tblWarehouse.strCity" & _
", tblWarehouse.strState, tblWarehouse.strZip"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I'm curious why your version did not throw an error or request you to
supply a value for a parameter named strOrgWarehouseID.
Click to expand...

because jet reads this
SELECT * FROM tblWarehouse Where strWarehouseID =
what ever the value strWarehouseID is
 
NO, JET would have no idea what the VBA variable strOrgWarehouseID was.
The only thing I can think of is that there is a field named
strOrgWarehouseID in the table and somehow that was matching the
strWareHouseID field in the same record of the table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top