SQL in VBA using multiple AND & OR?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have developed the following code from a query that I have confirmed
works. I want to now get it to run using VBA. The problem is that my AND
lines do not work. The OR lines are working though. Any help would be
appreciated:

Private Sub cboCompany_AfterUpdate()
cboCompany.Requery
cboWpnTypeEdit1 = Null

Dim strRType As String
Dim strCo As String

strCo = "SELECT DISTINCT TAMCN.[NOMEN], TAMCN.[ID], Serials.[Bn],
Serials.[Co] " & _
"FROM TAMCN " & _
"INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" & _
"WHERE Serials.Bn ='" & Me.cboBn.Value & "' " & _
"AND Serials.Bn ='" & Me.cboCompany.Value & "' " & _
"AND TAMCN.[ID] = '05538C'" & _
"OR TAMCN.[ID] = '05538D'" & _
"OR TAMCN.[ID] = '05538B'" & _
"OR TAMCN.[ID] = '02648A'" & _
"OR TAMCN.[ID] = '02468B'" & _
"OR TAMCN.[ID] = '05539B'" & _
"OR TAMCN.[ID] = '05539D'" & _
"OR TAMCN.[ID] = '09592A'" & _
"OR TAMCN.[ID] = '09629B'" & _
"OR TAMCN.[ID] = '10012B'" & _
"OR TAMCN.[ID] = '10012A'" & _
"OR TAMCN.[ID] = '02648C';"

cboWpnTypeEdit1.RowSource = strCo

strRType = "SELECT * FROM TAMCN "
strRType = strRType & " WHERE TAMCN.RowField = '" & cboCo & "'"

Me.RecordSource = strRType

cboWpnTypeEdit1.Requery

End Sub
 
First off, I cannot imagine that Serials.BN will ever equal the values of
both the Bn and Company combo boxes. I also think you need to make sure
that there are spaces between the criteria and the following "AND" and OR
functions. I would do this by putting a space between the quote and the AND
or OR. I also think you need a set of parenthesis. I also like to use
chr$(34) to delimit my text values rather than embedding quotes within
quotes. I would write it like:

strCo = "SELECT DISTINCT TAMCN.[NOMEN] " _
& ", TAMCN.[ID] " _
& ", Serials.[Bn]" _
& ", Serials.[Co]" _
& " FROM TAMCN " _
& " INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" _
&" WHERE Serials.Bn =" & chr$(34) & Me.cboBn.Value & chr$(34) _
& " AND Serials.Company =" & chr$(34) & Me.cboCompany.Value
& chr$(34) _
& " AND (TAMCN.[ID] = '05538C' " _
& " OR TAMCN.[ID] = '05538D' " _
& " OR TAMCN.[ID] = '05538B' " _
& " OR TAMCN.[ID] = '02648A' " _
& " OR TAMCN.[ID] = '02468B' " _
& " OR TAMCN.[ID] = '05539B' " _
& " OR TAMCN.[ID] = '05539D' " _
& " OR TAMCN.[ID] = '09592A' " _
& " OR TAMCN.[ID] = '09629B' " _
& " OR TAMCN.[ID] = '10012B' " _
& " OR TAMCN.[ID] = '10012A' " _
& " OR TAMCN.[ID] = '02648C') "

HTH
Dale

BTW You could replace all of the OR statements as follows:

strCo = "SELECT DISTINCT TAMCN.[NOMEN] " _
& ", TAMCN.[ID] " _
& ", Serials.[Bn]" _
& ", Serials.[Co]" _
& " FROM TAMCN " _
& " INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" _
&" WHERE Serials.Bn =" & chr$(34) & Me.cboBn.Value & chr$(34) _
& " AND Serials.Company =" & chr$(34) & Me.cboCompany.Value
& chr$(34) _
& " AND (TAMCN.[ID] IN ('05538C', '05538D', '05538B', " _
& " '02648A', '02468B', '05539B', '05539D', " _
& " '09592A', '09629B', '10012B', '10012A',
'02648C') "



Dan said:
I have developed the following code from a query that I have confirmed
works. I want to now get it to run using VBA. The problem is that my AND
lines do not work. The OR lines are working though. Any help would be
appreciated:

Private Sub cboCompany_AfterUpdate()
cboCompany.Requery
cboWpnTypeEdit1 = Null

Dim strRType As String
Dim strCo As String

strCo = "SELECT DISTINCT TAMCN.[NOMEN], TAMCN.[ID], Serials.[Bn],
Serials.[Co] " & _
"FROM TAMCN " & _
"INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" & _
"WHERE Serials.Bn ='" & Me.cboBn.Value & "' " & _
"AND Serials.Bn ='" & Me.cboCompany.Value & "' " & _
"AND TAMCN.[ID] = '05538C'" & _
"OR TAMCN.[ID] = '05538D'" & _
"OR TAMCN.[ID] = '05538B'" & _
"OR TAMCN.[ID] = '02648A'" & _
"OR TAMCN.[ID] = '02468B'" & _
"OR TAMCN.[ID] = '05539B'" & _
"OR TAMCN.[ID] = '05539D'" & _
"OR TAMCN.[ID] = '09592A'" & _
"OR TAMCN.[ID] = '09629B'" & _
"OR TAMCN.[ID] = '10012B'" & _
"OR TAMCN.[ID] = '10012A'" & _
"OR TAMCN.[ID] = '02648C';"

cboWpnTypeEdit1.RowSource = strCo

strRType = "SELECT * FROM TAMCN "
strRType = strRType & " WHERE TAMCN.RowField = '" & cboCo & "'"

Me.RecordSource = strRType

cboWpnTypeEdit1.Requery

End Sub
Click to expand...
 
Dale,
Thank you so much for taking the time to help. I have spent days looking
for what I had missed. The first thing was that I had it checking the
Serials.Bn twice. I should have caught that. Stupid mistake, usually is.
I had also looked for a way to combine the OR statements, but was unsure how
to do it, along with the placement of the () brackets. Your advise made all
the difference and ended many days of frustration. One last thing. The
chr$(34), is this using ASCI instead of the embedded quotes? Just trying to
learn more for the future.
Again, thanks for the help,
Dan
Dale Fye said:
First off, I cannot imagine that Serials.BN will ever equal the values of
both the Bn and Company combo boxes. I also think you need to make sure
that there are spaces between the criteria and the following "AND" and OR
functions. I would do this by putting a space between the quote and the AND
or OR. I also think you need a set of parenthesis. I also like to use
chr$(34) to delimit my text values rather than embedding quotes within
quotes. I would write it like:

strCo = "SELECT DISTINCT TAMCN.[NOMEN] " _
& ", TAMCN.[ID] " _
& ", Serials.[Bn]" _
& ", Serials.[Co]" _
& " FROM TAMCN " _
& " INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" _
&" WHERE Serials.Bn =" & chr$(34) & Me.cboBn.Value & chr$(34) _
& " AND Serials.Company =" & chr$(34) & Me.cboCompany.Value
& chr$(34) _
& " AND (TAMCN.[ID] = '05538C' " _
& " OR TAMCN.[ID] = '05538D' " _
& " OR TAMCN.[ID] = '05538B' " _
& " OR TAMCN.[ID] = '02648A' " _
& " OR TAMCN.[ID] = '02468B' " _
& " OR TAMCN.[ID] = '05539B' " _
& " OR TAMCN.[ID] = '05539D' " _
& " OR TAMCN.[ID] = '09592A' " _
& " OR TAMCN.[ID] = '09629B' " _
& " OR TAMCN.[ID] = '10012B' " _
& " OR TAMCN.[ID] = '10012A' " _
& " OR TAMCN.[ID] = '02648C') "

HTH
Dale

BTW You could replace all of the OR statements as follows:

strCo = "SELECT DISTINCT TAMCN.[NOMEN] " _
& ", TAMCN.[ID] " _
& ", Serials.[Bn]" _
& ", Serials.[Co]" _
& " FROM TAMCN " _
& " INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" _
&" WHERE Serials.Bn =" & chr$(34) & Me.cboBn.Value & chr$(34) _
& " AND Serials.Company =" & chr$(34) & Me.cboCompany.Value
& chr$(34) _
& " AND (TAMCN.[ID] IN ('05538C', '05538D', '05538B', " _
& " '02648A', '02468B', '05539B', '05539D', " _
& " '09592A', '09629B', '10012B', '10012A',
'02648C') "



Dan said:
I have developed the following code from a query that I have confirmed
works. I want to now get it to run using VBA. The problem is that my AND
lines do not work. The OR lines are working though. Any help would be
appreciated:

Private Sub cboCompany_AfterUpdate()
cboCompany.Requery
cboWpnTypeEdit1 = Null

Dim strRType As String
Dim strCo As String

strCo = "SELECT DISTINCT TAMCN.[NOMEN], TAMCN.[ID], Serials.[Bn],
Serials.[Co] " & _
"FROM TAMCN " & _
"INNER JOIN Serials ON TAMCN.[ID] = Serials.[ID]" & _
"WHERE Serials.Bn ='" & Me.cboBn.Value & "' " & _
"AND Serials.Bn ='" & Me.cboCompany.Value & "' " & _
"AND TAMCN.[ID] = '05538C'" & _
"OR TAMCN.[ID] = '05538D'" & _
"OR TAMCN.[ID] = '05538B'" & _
"OR TAMCN.[ID] = '02648A'" & _
"OR TAMCN.[ID] = '02468B'" & _
"OR TAMCN.[ID] = '05539B'" & _
"OR TAMCN.[ID] = '05539D'" & _
"OR TAMCN.[ID] = '09592A'" & _
"OR TAMCN.[ID] = '09629B'" & _
"OR TAMCN.[ID] = '10012B'" & _
"OR TAMCN.[ID] = '10012A'" & _
"OR TAMCN.[ID] = '02648C';"

cboWpnTypeEdit1.RowSource = strCo

strRType = "SELECT * FROM TAMCN "
strRType = strRType & " WHERE TAMCN.RowField = '" & cboCo & "'"

Me.RecordSource = strRType

cboWpnTypeEdit1.Requery

End Sub
Click to expand...
Click to expand...
 
Glad I could help.

Yes, 34 is the ascii code for the quotation mark ("). I don't like to use
the single quote (apostrophe) as a string delimeter because I frequently
encounter text fields with apostrophes (') imbedded in them, and unless you
replace them with two apostrophes, they will cause problems. Since I almost
never run into instances when clients embed quotes in a text string, I
prefer my method.

The other thing you can do is right after you finish building the SQL
string, you can add a line to your code:

debug.print strCo

and then put a break point after that statement. This will cause Access to
go into the code view and you can copy the text of the SQL string from the
immediate window. Then paste it into the SQL view of an Access query and
take a look at the way it looks in design view. If it is not exactly as you
expected, fiddle with the code in the code view, move the cursor up to the
line that builds the string again, and interate through this process until
the result looks like what you expect.

God Bless
 
Back
Top