Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2019, 05:13 AM   #1
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
on-the-fly Totals in Access Reports

I have created a report that is a template report (a basic list)
I have done this so i can call the report from various places without creating lots of similar reports.

when i want to use it i pass the query, column names and ordering to the report and in the 'on open' event i assign the recordsource and column names etc to the various field names

My On Open Code:

Buffer = Split(OpenArgs, "|")

'##### QUERY #####
Me.RecordSource = Buffer(0)

'##### TITLE #####
Me.lblTitle.Caption = Buffer(1)
Me.Caption = Buffer(2)

'##### ORDERING #####
Me.OrderByOn = True
Me.OrderBy = Buffer(3)
Me.lblOrdering.Caption = Buffer(4)

'##### FILTERS #####
Me.lblFilters.Caption = Buffer(5)

'##### HEADINGS #####
Me.lblCustom1.Caption = Buffer(6)
Me.lblCustom2.Caption = Buffer(7)
Me.lblCustom3.Caption = Buffer(8)
Me.lblCustom4.Caption = Buffer(9)
Me.lblCustom5.Caption = Buffer(10)
Me.lblCustom6.Caption = Buffer(11)

DoCmd.Maximize

I want to be able to have a total at the end of each column which totals each column IF its a numeric value (because you cannot sum a text field)

Any ideas how i could achieve this?

Buxtona is offline   Reply With Quote
Old 06-20-2019, 06:58 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Hi. Welcome to the forum. How about assigning the control source to another unbound control in the footer section of the page? Maybe something like:
Code:
Me.FirstTotalColumn="=Sum([ColumnName])"
Or, is the problem not knowing which column is numeric beforehand?
__________________
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 online now   Reply With Quote
Old 06-20-2019, 07:15 AM   #3
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

I need to check if the field is numeric first because when you try and sum a text field i get an error

Buxtona is offline   Reply With Quote
Old 06-20-2019, 07:16 AM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
I need to check if the field is numeric first because when you try and sum a text field i get an error
Have you tried using IsNumeric(), maybe?
__________________
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 online now   Reply With Quote
Old 06-20-2019, 07:31 AM   #5
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

yeah cant seem to get that working
Buxtona is offline   Reply With Quote
Old 06-20-2019, 07:33 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
yeah cant seem to get that working
Okay, maybe you can try using a TableDef object to check the data type of the field.
__________________
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 online now   Reply With Quote
Old 06-21-2019, 01:02 AM   #7
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

Ive tried adding this to the control source in one of the total columns:

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

This works on numeric values but not with text values

Buxtona is offline   Reply With Quote
Old 06-21-2019, 03:59 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
Ive tried adding this to the control source in one of the total columns:

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

This works on numeric values but not with text values
What happens with text values in this case? Do you get anything? How about adding a Val() function in there somewhere?
__________________
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 online now   Reply With Quote
Old 06-21-2019, 04:48 AM   #9
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

Error in attachment
Attached Images
File Type: jpg Error.jpg (27.8 KB, 15 views)
Buxtona is offline   Reply With Quote
Old 06-21-2019, 07:12 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
Error in attachment
Hi. That error message doesn't make sense. Let's try a simple check then. In the footer section of the page, try entering the following only:
Code:
=IsNumeric([Custom6a])
If you get the same error again, then there's something odd with the data in Custom6a; otherwise, I am expecting to get either a True or False in there.
__________________
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 online now   Reply With Quote
Old 06-21-2019, 07:37 AM   #11
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

If it is numeric i get '-1'

if it is not numeric i get '0'

Thats What i expected. Not sure why my command doesnt work.
Buxtona is offline   Reply With Quote
Old 06-21-2019, 07:42 AM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
If it is numeric i get '-1'

if it is not numeric i get '0'

Thats What i expected. Not sure why my command doesnt work.
Okay, that's good so far. Now, let's take it just one step further. Try:
Code:
=IIf(IsNumeric([Custom6a]),"True","False")
Or, try it this way:
Code:
=IIf(IsNumeric([Custom6a]),"True","")
Just isolating the True part...
__________________
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 online now   Reply With Quote
Old 06-23-2019, 11:25 PM   #13
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

both worked fine.

I reverted to:

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

but got the same error message.
Buxtona is offline   Reply With Quote
Old 06-24-2019, 07:00 AM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,429
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: on-the-fly Totals in Access Reports

Quote:
Originally Posted by Buxtona View Post
both worked fine.

I reverted to:

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

but got the same error message.
Hi. We may be going about this the wrong way. What do you get with this?
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 online now   Reply With Quote
Old 06-24-2019, 11:52 PM   #15
Buxtona
Newly Registered User
 
Join Date: Jun 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Buxtona is on a distinguished road
Re: on-the-fly Totals in Access Reports

Run-time error '3464'

Data type mismatch in criteria expression.

Buxtona 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 03:03 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