Creating a printed report containing multiple counts

[jim_s via AccessMonster.com]

Hi All,

Been trawling around for a solution to this problem. The problem is pretty
straightforward, yet surprisingly complicated to solve.

I have a statistical report that needs creating when demanded by the user.
The user enters a date range on the form in 2 text boxes and the values are
passed into queries(?). The stats report should then contain a load of count
statistics for how many records have been received/rejected/exported etc. in
that date range. The counts also have different conditions - imported records
for instance need to fit the date range entered by the user, but for the
exported records section of the report, records' export date needs to fit the
date range specified.

Using dCount on the report itself has proved useless. I have copied syntax
from other posts talking about dCount and copied code from Microsoft's own
website and Access doesn't like it. So, is there any way of getting these
counts without running about 40 queries and a load of queries based upon the
queries to get the counts?

Thanks in advance to anyone that helps!

 

[XPS35]

Hi All,

Been trawling around for a solution to this problem. The problem is pretty
straightforward, yet surprisingly complicated to solve.

I have a statistical report that needs creating when demanded by the user.
The user enters a date range on the form in 2 text boxes and the values are
passed into queries(?). The stats report should then contain a load of count
statistics for how many records have been received/rejected/exported etc. in
that date range. The counts also have different conditions - imported records
for instance need to fit the date range entered by the user, but for the
exported records section of the report, records' export date needs to fit the
date range specified.

Using dCount on the report itself has proved useless. I have copied syntax
from other posts talking about dCount and copied code from Microsoft's own
website and Access doesn't like it. So, is there any way of getting these
counts without running about 40 queries and a load of queries based upon the
queries to get the counts?

Thanks in advance to anyone that helps!

I don't see any reason (yet?) why DCounts won't work.

Without knowing your DCounts it is impossible to tell what is wrong.

 

[jim_s via AccessMonster.com]

DCount works fine as long as the input dates for the underlying query are
static. When the underlying query contains parameters (as passed from the
form to the query), the dCount query on top of this one refuses to work - it
seems to want to run the totals (without the parameters) first, then run the
query behind it which then asks for parameters.

Hi All,

[quoted text clipped - 21 lines]
I don't see any reason (yet?) why DCounts won't work.

Without knowing your DCounts it is impossible to tell what is wrong.

 

[John Spencer]

So, can you post one of the DCount calls that does not work?

I would expect to see something like the following. Assuming that you have an
open form that has two controls on it that contain the start and end of the
period.

=DCount("SomeField","SomeTable","SomeDateField Between " &
Format(Forms![YourFormname]![PeriodStart],"\#yyyy-mm-dd\#") & " AND "
Format(Forms![YourFormname]![PeriodEnd],"\#yyyy-mm-dd\#") )

Of course, using DCOUNT might not be best way to get the counts you need. A
query might be. But you have not shared any details on what you need to do.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

DCount works fine as long as the input dates for the underlying query are
static. When the underlying query contains parameters (as passed from the
form to the query), the dCount query on top of this one refuses to work - it
seems to want to run the totals (without the parameters) first, then run the
query behind it which then asks for parameters.

Hi All,

[quoted text clipped - 21 lines]I don't see any reason (yet?) why DCounts won't work.

Without knowing your DCounts it is impossible to tell what is wrong.

 

[jim_s via AccessMonster.com]

I've actually worked around this now by creating two queries for each stat -
one to take a recordset out and the second one to count it. Thanks for the
reply anyway John- to be honest it looks like that dCount would work rather
than the one I was using. My dCount assumed that because the values entered
in the text boxes were formatted in short date, Access would already pass the
format over to queries as opposed to dCount or the queries actually needing
to be specified the format.

Which coincidenally brings me to another problem. Although the solution I
came up with does work, it's not passing the dates correctly to the queries
(before I was testing it by entering parameters from a prompt box). I believe
you can specify in a query what format to read the value as, but this would
involve a lot of work to change 50+ queries. Ah well.

So, can you post one of the DCount calls that does not work?

I would expect to see something like the following. Assuming that you have an
open form that has two controls on it that contain the start and end of the
period.

=DCount("SomeField","SomeTable","SomeDateField Between " &
Format(Forms![YourFormname]![PeriodStart],"\#yyyy-mm-dd\#") & " AND "
Format(Forms![YourFormname]![PeriodEnd],"\#yyyy-mm-dd\#") )

Of course, using DCOUNT might not be best way to get the counts you need. A
query might be. But you have not shared any details on what you need to do.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

DCount works fine as long as the input dates for the underlying query are
static. When the underlying query contains parameters (as passed from the

[quoted text clipped - 10 lines]