From: jim_s via AccessMonster.com on
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1

From: XPS35 on
jim_s via AccessMonster.com wrote:

>
> 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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1
>

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

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

--
Groeten,

Peter
http://access.xps350.com

From: jim_s via AccessMonster.com on
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.

XPS35 wrote:
>> Hi All,
>>
>[quoted text clipped - 21 lines]
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1
>
>I don't see any reason (yet?) why DCounts won't work.
>
>Without knowing your DCounts it is impossible to tell what is wrong.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1

From: John Spencer on
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

jim_s via AccessMonster.com wrote:
> 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.
>
> XPS35 wrote:
>>> Hi All,
>>>
>> [quoted text clipped - 21 lines]
>>> Message posted via AccessMonster.com
>>> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1
>> I don't see any reason (yet?) why DCounts won't work.
>>
>> Without knowing your DCounts it is impossible to tell what is wrong.
>>
>
From: jim_s via AccessMonster.com on
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.

John Spencer wrote:
>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]
>>>
>>> Without knowing your DCounts it is impossible to tell what is wrong.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1