I *KNOW* there's an easy way to do this -- record count

[Gary T via AccessMonster.com]

I've created a query from a date field that selects records between two dates.
It works just fine. I now need to get a record count of this information.
When I use the count function it tells me I can't use an aggregate function
with this type of data.

Anyone?

 

[Allen Browne]

If you want to programmatically find out how many records are returned by
Query1, you could try:
=DCount("*", "Query1")

 

[Guest]

Are you talking about a select that looks like:

Select count(*) from YourTable
where [YourTable].[theDate] between #<date1># and #<date2>#

What does your query look like? How/Where are you trying to use Count()?

 

[Gary T via AccessMonster.com]

Are you talking about a select that looks like:

Select count(*) from YourTable
where [YourTable].[theDate] between #<date1># and #<date2>#

What does your query look like? How/Where are you trying to use Count()?

Looks like this:

SELECT Count(TRAINING.Entry_Date) AS CountOfEntry_Date
FROM TRAINING
HAVING (((Count(TRAINING.Entry_Date)) Between DateValue([Enter the beginning
date]) And DateValue([Enter the cutoff date])));

When I run it, it pulls up nothing.

 

[Guest]

Replace the HAVING clause with the following:

Where TRAINING.Entry_Date between [Enter the Beginning Date] and [Enter The
Ending Date]

What you have is asking for the count to be between the two dates.

--
Chaim

Are you talking about a select that looks like:

Select count(*) from YourTable
where [YourTable].[theDate] between #<date1># and #<date2>#

What does your query look like? How/Where are you trying to use Count()?

Looks like this:

SELECT Count(TRAINING.Entry_Date) AS CountOfEntry_Date
FROM TRAINING
HAVING (((Count(TRAINING.Entry_Date)) Between DateValue([Enter the beginning
date]) And DateValue([Enter the cutoff date])));

When I run it, it pulls up nothing.

 

[Gary T via AccessMonster.com]

Replace the HAVING clause with the following:

Where TRAINING.Entry_Date between [Enter the Beginning Date] and [Enter The
Ending Date]

What you have is asking for the count to be between the two dates.
[quoted text clipped - 11 lines]

When I run it, it pulls up nothing.

That's got it!
Thanks a lot... I owe ya.

 

[Gary T via AccessMonster.com]

Replace the HAVING clause with the following:

Where TRAINING.Entry_Date between [Enter the Beginning Date] and [Enter The
Ending Date]

What you have is asking for the count to be between the two dates.
[quoted text clipped - 11 lines]

When I run it, it pulls up nothing.

Is there a way to combine 3 of these queries into one?

 

[Gary T via AccessMonster.com]

Replace the HAVING clause with the following:

[quoted text clipped - 8 lines]
Is there a way to combine 3 of these queries into one?

DUH! I figured it out. Geeez... I looked RIGHT AT IT!

 

[Guest]

If all three queries have the same columns, you can use a UNION or UNION ALL
to combine them. It would look something like:

Query 1 UNION Query 2 UNION Query 3.

The restriction is that each Query must have the same number, type and order
of fields in the respective field lists; i.e., if Query 1 asks for a numeric
and a date, than the other 2 must also.

The difference between UNION and UNION ALL is the handling of duplicate
records. UNION will remove them; UNION ALL, as the name implies, shows them
all.

And you don't owe me at all. This is simply a great place and way to learn
and expand horizons. There are some things that people ask questions about
that I never thought of doing.
--
Chaim

Replace the HAVING clause with the following:

Where TRAINING.Entry_Date between [Enter the Beginning Date] and [Enter The
Ending Date]

What you have is asking for the count to be between the two dates.

Are you talking about a select that looks like:

[quoted text clipped - 11 lines]

When I run it, it pulls up nothing.

Is there a way to combine 3 of these queries into one?

 

[John Vinson]

Replace the HAVING clause with the following:

Where TRAINING.Entry_Date between [Enter the Beginning Date] and [Enter The
Ending Date]

What you have is asking for the count to be between the two dates.

Are you talking about a select that looks like:

[quoted text clipped - 11 lines]

When I run it, it pulls up nothing.

Is there a way to combine 3 of these queries into one?

You'll have to explain the three queries, Gary!

John W. Vinson[MVP]