Can Someone Tell me why this Query does not work (1 Viewer)

ppierce

Registered User.
Local time
Yesterday, 22:16
Joined
Nov 18, 2009
Messages
21
I have this query, shown below, in my Access code behind below that should return one record. But it is coming back as empty. Can anyone see what my problem is

Code:
StrSql = "SELECT tblOrders.*, tblFleetVendors.* FROM tblOrders INNER JOIN tblFleetVendors ON tblOrders.VendorId = tblFleetVendors.VendorID"
              StrSql = StrSql & " WHERE ((tblOrders.OrderingOrg)=" & Section & ") AND ((tblOrders.VendorId)<>774) AND ((tblOrders.OrderID)=" & Req & ");"
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:16
Joined
Mar 14, 2017
Messages
8,738
Wouldn't the ability to answer that question be very dependent on the value of all those variables you are referencing? I recommend troubleshooting from that perspective first, which should be done with a combination of breakpoints, and the Immediate Window (or debug.print if you prefer). When your debugging provides you what that literal value of StrSql is, then paste that into a Query window > sql view > design view in order to better evaluate whether that was the logic you wanted.
 

plog

Banishment Pending
Local time
Yesterday, 21:16
Joined
May 11, 2011
Messages
11,613
Code:
StrSql = "SELECT tblOrders.*, tblFleetVendors.* FROM tblOrders INNER JOIN tblFleetVendors ON tblOrders.VendorId = tblFleetVendors.VendorID"
              StrSql = StrSql & " WHERE ((tblOrders.OrderingOrg)=" & Section & ") AND ((tblOrders.VendorId)<>774) AND ((tblOrders.OrderID)=" & Req & ");"


First, that's not a query, it's the formula to build a VBA string. I'm not being pedantic, I'm raising a very important point--you haven't actually posted a query. The query would be whatever is actually inside StrSql whenever you add all the variable values to it. Go find that and then you should be able to better debug your query.

With that said, I bet you need single quotes around whatever is in Section. If that doesn't do it, you probably have unexpected values in Section and Req.

Again, though, find out what exactly is in the query you are building.
 

June7

AWF VIP
Local time
Yesterday, 18:16
Joined
Mar 9, 2014
Messages
5,423
Also, all those parens Access throws into WHERE clause are not needed. Shouldn't hurt but if you find them annoying, just remove.
 

ppierce

Registered User.
Local time
Yesterday, 22:16
Joined
Nov 18, 2009
Messages
21
Thank you very much for your input. I have no experience using the Access VBA and have inherited this project from an ex employee. So your assistance is really appreciated.

I have set up a watch window and I see the problem is the "Section" variable is not formatted correctly. It needs the single quotes. So I modified the String so it now looks like this:

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Section & "'") And (VendorID <> 774) And (tblOrders.OrderID = " & Req & ")"

However this code does not compile I get a Compile error: "Expected: End of Statement", and the paren, which I have highlighted above in blue, above is highlighted in blue. I am not sure what would be missing or what and where the End of Statement would need to be place.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:16
Joined
Feb 28, 2001
Messages
27,001
I think your problem is here (look at red):

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Section & "'") And (VendorID <> 774) And (tblOrders.OrderID = " & Req & ")"

Try

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Section & "') And (VendorID <> 774) And (tblOrders.OrderID = " & Req & ")"

I would have put that in code tags but code tags seem to eat color highlights.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:16
Joined
Jan 23, 2006
Messages
15,364
Try:
Code:
strSQL = strSQL & " WHERE (tblOrders.OrderingOrg = '" & section & "') And (VendorID <> 774) And (tblOrders.OrderID = " & req & ")"

assuming Req is numeric
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:16
Joined
May 7, 2009
Messages
19,169
where does Section comes from. if on same form use Me, else qualify with Forms!formname.
Section is reserved word.

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Me![Section] & "') And (VendorID <> 774) And (tblOrders.OrderID = " & Me![Req] & ")"
 

ppierce

Registered User.
Local time
Yesterday, 22:16
Joined
Nov 18, 2009
Messages
21
I think your problem is here (look at red):

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Section & "'") And (VendorID <> 774) And (tblOrders.OrderID = " & Req & ")"

Try

StrSql = StrSql & " WHERE (tblOrders.OrderingOrg = '" & Section & "') And (VendorID <> 774) And (tblOrders.OrderID = " & Req & ")"

I would have put that in code tags but code tags seem to eat color highlights.

Thank You to The_Doc_Man that was the fix it is now functioning as expected.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:16
Joined
Feb 28, 2001
Messages
27,001
Great !!! Happy to have helped. Come back again when you have more issues.
 

Users who are viewing this thread

Top Bottom