Creating a printed report containing multiple counts
in [Reports]
|
Prev: Controlling printed records when report bound to multiple tables
Next: Report using Fields with the same name from different tables
From: jim_s via AccessMonster.com on 19 May 2010 05:48 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 19 May 2010 07:29 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 19 May 2010 07:45 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 19 May 2010 09:47 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 20 May 2010 05:39
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 |