Simple SQL Help Needed with DAO

[Brandon Johnson]

im getting a runtime error of 3061 to few perameters. Any ideas that
would help my situation?

Set extract = CurrentDb.OpenRecordset("SELECT * FROM
qryZerosAndBlanksVoids WHERE [Invoice_Week] >= '611' AND [UPCCase] IN
('4450005086', '4450005089', '4450005279') AND [InvQty] IN ('0', 'IS
NULL');", dbOpenDynaset)

 

[Carl Rapson]

What is the data type of the field [InvQty]? If it's a number, your criteria
won't work as is. What you will need is something like

AND ([InvQty] IS NULL OR [InvQty]=0)

Or you can combine them into

AND Nz([InvQty],0)=0

If the other two fields are numeric also, you don't want to use single
quotes around the values.

Carl Rapson

 

[Brandon Johnson]

Thankyou for your speedy response. I have changed them now and it still
dont work out. My invoice_week is a text in my table, my UPCCase is a
text also, and my InvQty is an Integer. Any other thoughts that you may
have would be appreciated.

Set extract = CurrentDb.OpenRecordset("SELECT * FROM
qryZerosAndBlanksVoids WHERE [Invoice_Week] >= '611' AND ([UPCCase] IN
('4450005086', '4450005089', '4450005279')) AND ([InvQty] = 0 OR
[InvQty] IS NULL);", dbOpenDynaset)

What is the data type of the field [InvQty]? If it's a number, your criteria
won't work as is. What you will need is something like

AND ([InvQty] IS NULL OR [InvQty]=0)

Or you can combine them into

AND Nz([InvQty],0)=0

If the other two fields are numeric also, you don't want to use single
quotes around the values.

Carl Rapson

im getting a runtime error of 3061 to few perameters. Any ideas that
would help my situation?

Set extract = CurrentDb.OpenRecordset("SELECT * FROM
qryZerosAndBlanksVoids WHERE [Invoice_Week] >= '611' AND [UPCCase] IN
('4450005086', '4450005089', '4450005279') AND [InvQty] IN ('0', 'IS
NULL');", dbOpenDynaset)

 

[OfficeDev18 via AccessMonster.com]

Brandon,

The error message points to text that appears in [ ] brackets. Check the
query qryZerosAndBlanksVoids. I bet one of the bracketed fields in your SQL
statement, namely [Invoice_Week], [UPCCase], or [InvQty] is not spelled
EXACTLY the same way as in the query. Also, if the query
qryZerosAndBlanksVoids is a totals query, and [InvQty] is a Count or Sum
aggregate function, it might be called SumOfInvQty or CountOfInvQty when you
run the qryZerosAndBlanksVoids query in DataSheet view. In that case you
should change the field [InvQty] in your SQL accordingly.

Hope this helps,

Sam

Thankyou for your speedy response. I have changed them now and it still
dont work out. My invoice_week is a text in my table, my UPCCase is a
text also, and my InvQty is an Integer. Any other thoughts that you may
have would be appreciated.

Set extract = CurrentDb.OpenRecordset("SELECT * FROM
qryZerosAndBlanksVoids WHERE [Invoice_Week] >= '611' AND ([UPCCase] IN
('4450005086', '4450005089', '4450005279')) AND ([InvQty] = 0 OR
[InvQty] IS NULL);", dbOpenDynaset)

What is the data type of the field [InvQty]? If it's a number, your criteria
won't work as is. What you will need is something like

[quoted text clipped - 17 lines]

('4450005086', '4450005089', '4450005279') AND [InvQty] IN ('0', 'IS
NULL');", dbOpenDynaset)