Date Range Count Query Not Working

[delsay]

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 "CountOfPurchas eDate" with no value below.

Please help!

[Killer42]

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 "CountOfPurchas eDate" 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.

[Killer42]

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).

[NeoPa]

If you have SQL then similar to :-

SELECT Count([PurchaseDate]) AS Total
FROM [SomeTable]
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?

[MMcCarthy]

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 "CountOfPurchas eDate" 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]);

[NeoPa]

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(PurchaseD ate) will count all the non-null occurrences of PurchaseDate which should normally be equivalent to a record count of the specified records.

[Killer42]

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(PurchaseD ate) 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.