Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-25-2019, 03:12 AM   #16
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,349
Thanks: 159
Thanked 1,706 Times in 1,676 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: on-the-fly Totals in Access Reports

I think Access will still try and evaluate the Sum() expression in a IIf() before producing the result, even if it's not needed.

Try writing a function to accommodate it instead?

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-25-2019, 07:11 AM   #17
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
Run-time error '3464'

Data type mismatch in criteria expression.
Hi. Okay, one last try before I ask for a sample file to play with. How about?
Code:
=Sum(Val(Nz([Custom6a],0)))
or
Code:
=Sum(IIf(IsNumeric([Custom6a]),[Custom6a],0))
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-25-2019, 07:27 AM   #18
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

=Sum(Val(Nz([Custom6a],0)))

This one doesnt quite work because if there are values in a text field it sums those

=Sum(IIf(IsNumeric([Custom6a]),[Custom6a],0))

This one seems to work. as long as the output is numeric i get the correct result
(Could just set conditional formatting on the total field to have white text if total = 0)

Buxtona is offline   Reply With Quote
Old 06-25-2019, 08:05 AM   #19
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
=Sum(Val(Nz([Custom6a],0)))

This one doesnt quite work because if there are values in a text field it sums those

=Sum(IIf(IsNumeric([Custom6a]),[Custom6a],0))

This one seems to work. as long as the output is numeric i get the correct result
(Could just set conditional formatting on the total field to have white text if total = 0)
Hi. Glad to hear you we got something usable now, even if it's not perfect. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Buxtona (06-26-2019)
Old 06-25-2019, 08:24 AM   #20
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,997
Thanks: 20
Thanked 372 Times in 365 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
=Sum(Val(Nz([Custom6a],0)))

This one doesnt quite work because if there are values in a text field it sums those

=Sum(IIf(IsNumeric([Custom6a]),[Custom6a],0))

This one seems to work. as long as the output is numeric i get the correct result
(Could just set conditional formatting on the total field to have white text if total = 0)
If your SUM returns 0, you can wrap it all up. Set the height of this control to 0 and add an extra control for display. Other control would be
Code:
=IIF(Me.YourSumControl = 0, "", Me.YourSum Control)
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Buxtona (06-26-2019)
Old 06-26-2019, 12:23 AM   #21
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

thanks guys. I think i can get something that works now instead of using several different reports. much appreciated
Buxtona is offline   Reply With Quote
Old 06-26-2019, 03:59 AM   #22
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
thanks guys. I think i can get something that works now instead of using several different reports. much appreciated
Hi. Were happy to assist. Good luck with your project.


__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
totals on reports Sanjo Reports 3 03-16-2014 12:01 PM
Totals in reports trying Forms 5 08-15-2012 06:37 AM
Totals in Reports RexesOperator Reports 11 08-08-2007 05:19 PM
summing totals in reports bakerboy_1111 Reports 2 12-23-2003 05:24 PM
Sum Totals in Reports ?! John Bull Reports 5 09-12-2000 09:51 AM




All times are GMT -8. The time now is 02:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World