473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
3 4317
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
254 Expert 100+
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
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

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

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
20
by: Deano | last post by:
Just looking at C Sharp to see if it might be worth my while learning something new. Has anyone here tried a .NET language and tried to replicate a existing Access app? I would be interested to...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
7
by: Cheryl Langdon | last post by:
Does anyone know if there is a way to globally turn off ALL control tips in Access 2003 using VBA code? Thanks. --- CL
37
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why...
4
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
3
by: Sheldon | last post by:
I have the following query expression - Like Format((!!)) & "/*/" & (! !) which would translate to e.g. 04/*/2007 if someone is running a report for last month. The above expression is part of a...
2
by: nina | last post by:
is there a way to group records in forms in 2003 version ie. i have number of records with date and would like to see it in this way on a form sep 2007 rec1 rec2 rec4 .. ..
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.