A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a printed report containing multiple counts



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 10:48 AM posted to microsoft.public.access.reports
jim_s via AccessMonster.com
external usenet poster
 
Posts: 7
Default Creating a printed report containing multiple counts

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/For...ports/201005/1

Ads
  #2  
Old May 19th, 2010, 12:29 PM posted to microsoft.public.access.reports
xps35
external usenet poster
 
Posts: 22
Default Creating a printed report containing multiple counts

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/For...ports/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

  #3  
Old May 19th, 2010, 12:45 PM posted to microsoft.public.access.reports
jim_s via AccessMonster.com
external usenet poster
 
Posts: 7
Default Creating a printed report containing multiple counts

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/For...ports/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/For...ports/201005/1

  #4  
Old May 19th, 2010, 02:47 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Creating a printed report containing multiple counts

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/For...ports/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.


  #5  
Old May 20th, 2010, 10:39 AM posted to microsoft.public.access.reports
jim_s via AccessMonster.com
external usenet poster
 
Posts: 7
Default Creating a printed report containing multiple counts

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/For...ports/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.