on-the-fly Totals in Access Reports

[Buxtona]

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]

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:

Me.FirstTotalColumn="=Sum([ColumnName])"

Or, is the problem not knowing which column is numeric beforehand?

 

[Buxtona]

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 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]

yeah cant seem to get that working

 

[theDBguy]

yeah cant seem to get that working

Okay, maybe you can try using a TableDef object to check the data type of the field.

 

[Buxtona]

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]

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]

Error in attachment

 

[theDBguy]

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:

=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]

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]

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:

=IIf(IsNumeric([Custom6a]),"True","False")

Or, try it this way:

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

Just isolating the True part...

 

[Buxtona]

both worked fine.

I reverted to:

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

but got the same error message.

 

[theDBguy]

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?

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

 

[Buxtona]

Run-time error '3464'

Data type mismatch in criteria expression.

 

[Minty]

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]

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?

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

or

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

 

[Buxtona]

=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]

=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_]

=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

=IIF(Me.YourSumControl = 0, "", Me.YourSum Control)