By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Access 2003 SQL Show data between the first and last day of the previous month.

P: 3
Hi, I am pretty new to Access 2003 and SQL. I am trying to have a count show up in a query to show specific barcode prefix data between the first and the last day of the previous month. I want to do this for the remaining 5 months so I know I would change the -1 to a -2 ect. Anyways, for some reason this is not working and I get a data mismatch error. Here is what I have typed in SQL using Access 2003.





SELECT Count(tblComponentInventory.Date) AS Month5ValveCount
FROM tblComponentInventory
WHERE (((tblComponentInventory.Date) Between DateAdd("dd",-(Day(DateAdd("mm",1,Now()))-1),DateAdd("mm",-1,Now())) And DateAdd("dd",-Day(DateAdd("m",1,Now())),DateAdd("m",-1,Now()))) AND ((tblComponentInventory.EquipmentID) Like "VC*" Or (tblComponentInventory.EquipmentID) Like "VO*"));

Any help would be highly appreciated!



TIA
Jan 14 '08 #1
Share this Question
Share on Google+
3 Replies


P: 3
I meant I want a count of a specific barcode and the number of times during the last month it was scanned between the first and the last day of the previous month.
Jan 14 '08 #2

jaxjagfan
Expert 100+
P: 254
I meant I want a count of a specific barcode and the number of times during the last month it was scanned between the first and the last day of the previous month.
This would give you a count of all barcodes for last month based on today's date. If you want a specific one, just add the criteria to the WHERE clause. You will need to change the Barcode field name if different.

SELECT tblComponentInventory.Barcode,Count(tblComponentIn ventory.Date) AS Month5ValveCount
FROM tblComponentInventory
WHERE ((Month(tblComponentInventory.Date) = DateAdd("m",-1,Date()))
GROUP BY tblComponentInventory.Barcode
Jan 14 '08 #3

P: 3
Ok, this is helping. But how would I count the barcodes between the first and the last day of last month. Wouldn't what you gave me only count the records between now 1/14/08 and 12/14/07? I want to count all of the records for December. Thanks.
Jan 15 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.