Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 27th, 2007, 04:34 PM
Authorized User
Points: 135, Level: 2
Points: 135, Level: 2 Points: 135, Level: 2 Points: 135, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Dallas, TX, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report Issue

I've built a summary report that will count the number of records if certain fields contain a range of values (ex: vales = 0 through 7). I have the following code in the control source of one txt box within the report:

=Sum(IIf([cboBOQuestion1] Between "0" And "7" Or
[cboBOQuestion2] Between "0" And "7" Or
[cboBOQuestion3] Between "0" And "7" Or
[cboBOQuestion4] Between "0" And "7" Or
[cboBOQuestion5] Between "0" And "7" Or
[cboBOQuestion6] Between "0" And "7" Or
[cboBOQuestion7] Between "0" And "7" Or
[cboBOQuestion8] Between "0" And "7",1,0))

This seems to be too much for Access and will shut down on me. If I compare only 7 fields, it will run properly, but if I compare 8 fields, I run into trouble. Is there a way to simplify this code so that I can count the number of records where all 8 field values contain a value between 0 and 7?

  #2 (permalink)  
Old November 28th, 2007, 10:18 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Whatever section of the report this is in, try putting the code in the section's on format event and counting that way. I have used ADO recordsets to do large summing operations in report headers. Brute force, but works well enough if you can't find a solution.

Alternatively, can you do this counting in the query for the summary data?




mmcdonal

Look it up at: http://wrox.books24x7.com
  #3 (permalink)  
Old December 19th, 2007, 12:01 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One way to deal with excessively long expressions is to break them up by creating extra fields on the report that you can make invisible and place pieces of the expression in each one. Then you can use a simpler expression in your displayed field to just combine the values in these extra fields.

-Phil-
  #4 (permalink)  
Old January 4th, 2008, 09:27 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Hmmm... it seems that you just care if ANY of the eight are between "0" and "7". So if Question1 fits that criteria, why evaluate the rest? Try this construct and see if it works:

=Sum(IIf([cboBOQuestion1] Between "0" And "7", 1, IIf([cboBOQuestion2] Between "0" And "7", 1, IIf([cboBOQuestion3] Between "0" And "7", 1, IIf([cboBOQuestion4] Between "0" And "7", 1, IIf([cboBOQuestion5] Between "0" And "7", 1, IIf([cboBOQuestion6] Between "0" And "7", 1, IIf([cboBOQuestion7] Between "0" And "7", 1, IIf([cboBOQuestion8] Between "0" And "7", 1, 0))))))))


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division


Similar Threads
Thread Thread Starter Forum Replies Last Post
Matrix report issue aranjan BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 February 4th, 2007 02:42 AM
V V V Urgent::Crystal report format issue narayanark2003 Crystal Reports 0 July 6th, 2006 07:58 AM
Sub report shared prob issue kondapally Crystal Reports 0 May 12th, 2005 10:46 AM
Sub report issue kondapally Crystal Reports 2 February 9th, 2005 11:35 AM
Sub Report Issue bjoneskc01 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 January 21st, 2005 11:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.