473,289 Members | 1,780 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,289 software developers and data experts.

Date Range Count Query Not Working

1
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.

Right now, this is what I have:

Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992

When I run it, it returns the title "CountOfPurchaseDate" with no value below.

Please help!
Oct 31 '06 #1
6 6908
Killer42
8,435 Expert 8TB
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.

Right now, this is what I have:

Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992

When I run it, it returns the title "CountOfPurchaseDate" with no value below.

Please help!
What you have there so far should be giving you a count for each time (um... second, I think) for the whole of January 1992. In other words, something like (up to) 86,400 entries for each day, depending on your data.

I think you could try setting up a formatted field, and count that. Something like YYYYMM: Year([Purchase Date]) & Month([Purchase Date])
Than use it much the same way you stated above.
Oct 31 '06 #2
Killer42
8,435 Expert 8TB
What you have there so far should be giving you a count for each time (um... second, I think) for the whole of January 1992. In other words, something like (up to) 86,400 entries for each day, depending on your data.

I think you could try setting up a formatted field, and count that. Something like YYYYMM: Year([Purchase Date]) & Month([Purchase Date])
Than use it much the same way you stated above.
Sorry, I think I have that a bit garbled.

What I would try first (keeping in mind I'm not an Access expert) is to set up your formatted field made up of the year and month, group by that, and add a second field with a Count (I don't think it matters much what field you use for this part).
Oct 31 '06 #3
NeoPa
32,554 Expert Mod 16PB
If you have SQL then similar to :-
Expand|Select|Wrap|Line Numbers
  1. SELECT Count([PurchaseDate]) AS Total
  2. FROM [SomeTable]
  3. WHERE [PurchaseDate] Between #1/1/1992# And #1/31/1992#
and no records show, that impies you don't have any matching data.
What records do you have in the table that you'd expect to be included?
Oct 31 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.

Right now, this is what I have:

Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992

When I run it, it returns the title "CountOfPurchaseDate" with no value below.

Please help!
Try this:

SELECT Month([Purchase Date]), Count(Month([Purchase Date])), Year([Purchase Date]), Count(Year([Purchase Date]))
FROM SomeTable
WHERE Year([Purchase Date]) >= 1992
GROUP BY Month([Purchase Date]), Year([Purchase Date]);
Oct 31 '06 #5
NeoPa
32,554 Expert Mod 16PB
In Access you don't need to specify a GROUP BY clause if the grouping goes across the whole recordset (as restricted by the WHERE clause of course).
Count(PurchaseDate) will count all the non-null occurrences of PurchaseDate which should normally be equivalent to a record count of the specified records.
Oct 31 '06 #6
Killer42
8,435 Expert 8TB
In Access you don't need to specify a GROUP BY clause if the grouping goes across the whole recordset (as restricted by the WHERE clause of course).
Count(PurchaseDate) will count all the non-null occurrences of PurchaseDate which should normally be equivalent to a record count of the specified records.
Of course, when in doubt, you can just switch the query to a normal Select query and run it to see whether anything matches your criteria. I generally do that with my Delete queries as a final check before running them.
Oct 31 '06 #7

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

Similar topics

5
by: BlackFireNova | last post by:
I need to write a report in which one part shows a count of how many total records fall within the working days (Monday - Friday) inside of a (prompted) given date range, in a particular...
1
by: FatBoyThin | last post by:
I love it when I back myself into a corner. Seems to be the fastest way to learn. I have 3 tables; tblINBOX, tblOUTBOX and tblPENDING The relevant rows are as following; Date, userID userID is...
10
by: Robert | last post by:
I would like to run a report for each month over two years. I am currently using a date range like this. Then manually substitute the error_time bounds for each month and rerun the query. How...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
3
by: rugger81 | last post by:
I am currently working in the sql server 2000 environment and I want to write a function to pull all dates within a given date range. I have created several diferent ways to do this but I am...
7
by: No bother | last post by:
I have a table which has, among other fields, a date field. I want to get a count of records where certain criteria are met for, say, three days in a row. For example: NumWidgets Date...
2
by: sixdeuce62 | last post by:
Hello, I am trying to create a query that will prompt me to enter the parameter value if beginning date and ending date. I have created everything I need in the query, but I have to manually go...
1
by: devyon122 | last post by:
I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query: TRANSFORM Count(imagingdb.ID) AS CountOfID TRANSFORM...
5
by: jambonjamasb | last post by:
I am wanting to create a report that summarises the number of items within a date range. For example I have a FIELD called System_Change. This is a drop down COMBOBOX that uses words like unix,...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.