Query using sql with several WHERE conditions

[MJD1]

hi all, need some help with something I haven't used in the past. I need to use vba to pass a query which contains two conditions. Here's what I have so for but it doesn't work. I can't see where i've gone wrong so hoping to get so help.

I need to pull all records where the field "Claim_New" = 0 and where the field "Completed" = 0



Dim sql As String, qdf As Dao.QueryDef

Set qdf = CurrentDb.QueryDefs("qry_dc_claims")
qdf.sql = "SELECT masterboltracker.ID, masterboltracker.batch, masterboltracker.datedelivery, masterboltracker.store, masterboltracker.bol, masterboltracker.sku, masterboltracker.description, masterboltracker.skuontag, masterboltracker.shipped, masterboltracker.received, masterboltracker.variance, masterboltracker.completed, masterboltracker.extended, masterboltracker.dept, masterboltracker.from_dc_number, masterboltracker.claim_new " & _
"From masterboltracker " & _
"WHERE masterboltracker.claim_new = 0, masterboltracker.completed = 0;"



thanks in advance!

 

[PHV]

WHERE masterboltracker.claim_new = 0 [!]AND[/!] masterboltracker.completed = 0;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MJD1]

Thanks. So obvious..

 

[MJD1]

hi, need a bit more guidance. The below works..except that the "And masterboltracker.claim_new =" & 1 & _" isn't being executed. the output should only be 1 record, however, all 20 test records continue to show. I'm not getting any error messages and the ""Where masterboltracker.completed =" & 0 & " does filter out the one record.

Hoping someone can help.


Set qdf = CurrentDb.QueryDefs("qry_dc_claims")
qdf.sql = "SELECT masterboltracker.ID, masterboltracker.batch, masterboltracker.datedelivery, masterboltracker.store, masterboltracker.bol, masterboltracker.sku, masterboltracker.description, masterboltracker.skuontag, masterboltracker.shipped, masterboltracker.received, masterboltracker.variance, masterboltracker.extended, masterboltracker.dept, masterboltracker.from_dc_number, masterboltracker.completed, masterboltracker.from_dc_number, masterboltracker.claim_new " & _
"From masterboltracker " & _
"Where masterboltracker.completed =" & 0 & _
"And masterboltracker.claim_new =" & 1 & _ <---------------------------- This is the issue
"And masterboltracker.from_dc_number =" & 524 & _
"Or masterboltracker.from_dc_number =" & 1003 & _
"Or masterboltracker.from_dc_number =" & 1004 & _
"Or masterboltracker.from_dc_number =" & 1006 & _
"Or masterboltracker.from_dc_number =" & 1007 & _
"Or masterboltracker.from_dc_number =" & 1014 & _
"Or masterboltracker.from_dc_number =" & 1051 & _
"Or masterboltracker.from_dc_number =" & 1052 & _
"Or masterboltracker.from_dc_number =" & 1053 & _
"Or masterboltracker.from_dc_number =" & 1071 & _
"Or masterboltracker.from_dc_number =" & 1072 & _
"Or masterboltracker.from_dc_number =" & 1073 & _
"Or masterboltracker.from_dc_number =" & 1074 & _
"Or masterboltracker.from_dc_number =" & 1075 & _
"Or masterboltracker.from_dc_number =" & 1106 & _
"Or masterboltracker.from_dc_number =" & 1107 & _
"Or masterboltracker.from_dc_number =" & 1180 & _
"Or masterboltracker.from_dc_number =" & 1181 & _
"Or masterboltracker.from_dc_number =" & 1182 & _
"Or masterboltracker.from_dc_number =" & 1083 & _
"Or masterboltracker.from_dc_number =" & 1188


'"Order by masterboltracker.batch"

Me.Combo3.RowSource = "qry_dc_Claims"



thanks in advance
Martin

 

[dhookom]

I think you want this:

qdf.sql = "SELECT ID, batch, datedelivery, store, bol, sku, description, " & _
    "skuontag, shipped, received, variance, extended, dept, from_dc_number, " & _
    "completed, from_dc_number, claim_new " & _
 "From masterboltracker " & _
 "Where completed = 0 And claim_new = 1 " & _  
 "And from_dc_number IN (524, 1003, 1004, 1006, 1007, 1014, 1051, 1052, " & _
 "1053, 1071, 1072, 1073, 1074, 1075, 1106, 1107, 1180, 1181, 1182, 1083, 1188 )"

I typically try to avoid hard-coding values like this into queries. I would prefer to see something in a table of from_dc_numbers that would identify their inclusion.

Duane
Hook'D on Access
MS Access MVP

 

[MJD1]

hi, thanks so much. following your recommendation, I modified the code with the below, but i'm getting a syntax error.
I created a dc_number table that I have Text6 (which is combobox). I have it retreive the dc_name and dc_numbers. it's bound to column 2. The DC_number field in the table is set as text and I entered the location like this 1006, 1007, 1008 ect.



Set qdf = CurrentDb.QueryDefs("qry_dc_claims")

qdf.sql = "SELECT ID, batch, datedelivery, store, bol, sku, description, " & _
"skuontag, shipped, received, variance, extended, dept, from_dc_number, " & _
"completed, from_dc_number, claim_new " & _
"From masterboltracker " & _
"Where completed = 0 And claim_new = 1 " & _
"And from_dc_number IN = " & Me.Text6


thanks again!

 

[dhookom]

What is the value of Me.Text6?

Try add debug.print to see what's going on.

The "IN ()" expression expects () and one or more values separated by commas. It does not use the equal sign.

Dim strSQL as String
Set qdf = CurrentDb.QueryDefs("qry_dc_claims")
strSQL =  "SELECT ID, batch, datedelivery, store, bol, sku, description, " & _
 "skuontag, shipped, received, variance, extended, dept, from_dc_number, " & _
 "completed, from_dc_number, claim_new " & _
 "From masterboltracker " & _
 "Where completed = 0 And claim_new = 1 " & _
 "And from_dc_number IN = " & Me.Text6
 debug.Print strSQL
 qdf.sql = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[MJD1]

Thanks, I removed the = sign but still getting the error message. the me.text6 contains the string of numbers seperated by a comma, which is in a table, with the field formatted as text. (524, 1003, 1004, 1006, 1007, 1014, 1051, 1052, 1053, 1071, 1072, 1073, 1074, 1075, 1106, 1107, 1180, 1181, 1182, 1083, 1188 ).

 

[MJD1]

ok, problem fixed by doing this

"And from_dc_number IN " & "(" & Me.Text6 & " )"

thanks for your help and guidance!!

martin

 

[dhookom]

You are given only a limited number of quotes in your lifetime and you are burning some with :

 "And from_dc_number IN " & "(" & Me.Text6 & " )"

which could be

 "And from_dc_number IN (" & Me.Text6 & " )"

Duane
Hook'D on Access
MS Access MVP