Simple sum field on report not working. (1 Viewer)

mafhobb · Nov 16, 2017

Hello, I have created an invoice using a report that draws its data from a query and places it in text boxes. The data displays fine.

On the report's page footer (and report footer, I have tried both) I have added another text box where I intend to sum the running total of one of the text boxes.

The text box in the report detail that gets the data from the query is called "txtHistoryHours" and the textbox where I want to add them all is called "txtSubtotal". The source for this box is "=Sum([txtHistoryHours])"

When I run this, I get "#Error# in txtSubtotal.

What am I doing wrong?

mafhobb

isladogs · Nov 16, 2017

What datatype is used for the field shown in txtHistoryHours?
If its date/time, convert to a number using CDbl then sum that

Code:

=Sum(CDbl(txtHistoryHours))

That should give a value in seconds

You can convert it back by using CDate

Code:

=CDate(Sum(CDbl(txtHistoryHours)))

or convert to hours by dividing by 3600 etc

arnelgp · Nov 16, 2017

you do your sum on fieldname and not by textbox name.

mafhobb · Nov 16, 2017

Ridders, both of the fields contain numbers and in the tables that they belong to, they are defined as numbers

arnelgp, I have tried to change to fieldname and I received the same error.

I have also tried to multiply this field with other field in the same report to get a calculated field and that worked fine. What does not seem to work is when I add the function "SUM" and move the results box in the page or report footer

mafhobb

isladogs · Nov 16, 2017

arnel is of course correct about using field names not control names.

However my point about date fields is also correct.
For number fields, the sum SHOULD work fine but not for Date/Time fields

Attached is a very simple DEMO to explain what I mean.

I have a table with a field called DateTimeField & another called NumberField.
The values in each record are equivalent

The report shows what happens if you just sum them both
Also how to correctly add the date time field

HTH

mafhobb · Nov 16, 2017

Still struggling with this seemingly simple thing. I have modified my query so it does some of the calculations itself and the fields get filled out correctly, but I still need a running sum and I only get #Error.

The source for the running sum field is =Sum([HistoryHours]). HistoryHours is a number field, with its data pulled from a query and the Running sum setting for the field is set to "All".

I have no clue why this does not work.

mafhobb · Nov 16, 2017

I think that I got it. It all seems to work if I place it in the report footer, but it does not work if I place it in the page footer.

mafhobb

isladogs · Nov 16, 2017

Did you look at my example report that I supplied?
I have the running sum set to 'No' in each case

mafhobb · Nov 16, 2017

ridders, yes, I did look at your examples but still I could not make it work until I moved the running sum fields to the report footer after noticing that this is how you had it.

Thank you for your help! I will keep your db example for future reference.

mafhobb

Pat Hartman · Nov 17, 2017

When you use a calculated expression in a control source, you must reference data fields from the RecordSource NOT the names of textboxes. So:

=Sum([txtHistoryHours])

would have to be what the original expression was.

=Sum((fldA * fldB) /fldC) --- for example

Replace "txtHistoryHours" with whatever is in the ControlSource for txtHistoryHours

Simple sum field on report not working. (1 Viewer)

mafhobb

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

mafhobb

Registered User.

isladogs

MVP / VIP

Attachments

mafhobb

Registered User.

mafhobb

Registered User.

isladogs

MVP / VIP

mafhobb

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread