on-the-fly Totals in Access Reports (1 Viewer)

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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?
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
I need to check if the field is numeric first because when you try and sum a text field i get an error
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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?
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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?
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
Error in attachment
 

Attachments

  • Error.jpg
    Error.jpg
    27.8 KB · Views: 108

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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.
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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...
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
both worked fine.

I reverted to:

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

but got the same error message.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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)
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
Run-time error '3464'

Data type mismatch in criteria expression.
 

Minty

AWF VIP
Local time
Today, 12:01
Joined
Jul 26, 2013
Messages
10,354
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
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))
 

Buxtona

Registered User.
Local time
Today, 12:01
Joined
Jun 20, 2019
Messages
17
=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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:01
Joined
Oct 29, 2018
Messages
21,357
=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!
 

Mark_

Longboard on the internet
Local time
Today, 05:01
Joined
Sep 12, 2017
Messages
2,111
=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)
 

Users who are viewing this thread

Top Bottom