Adding Expressions in a Report?

[BlueChicken]

Hello everyone! Back for more help:

Here is my problem now:

I am trying to add up the complete costs of the tickets we book so that we can produce a report to our accounting department.

Using Google I figured out how to get the totals for each "ticket" (The 1, 2, or 3 legged trips going only 1 way) and then the group totals for each "trip" (The totals of the tickets going both ways). All of that works perfectly. However, the trouble comes when I try to add the totals from two trips together.

I can't seem to find an expression that works. I either end up with an '# Error' or a 0 in the text box where the expression is.

I am pretty sure this SUM can be calculated, and as I keep getting it wrong I am beginning to think that it is going to be solved using code... if anyone can help me figure this out I'd greatly appreciate it.

I attached two pictures of the report, one in design view and one in print preview.

The textboxes in question are named "SumOfTicketCosts" (This is the query based calculation of the ticket costs), "TripCostSUM" (the sum of each separate trip), and "TotalTicketCosts" *(as the name says).

It is the "TotalTicketCosts" That is not working.

Need anything else, just let me know!

 

[SOS]

Just put

=Sum([SumOfTicketCosts])

In the footer as well and it will give you the total for it.

 

[BlueChicken]

Just put

=Sum([SumOfTicketCosts])

In the footer as well and it will give you the total for it.

I get "# Error"

 

[SOS]

Make sure that the original text box is not named the same (change it to txtSumOfTicketCosts and then make sure that all of your sums are referencing the FIELD - SumOfTicketCosts).

You basically have the same text box with the same formula but placed in each footer and it will do the trick but the name of the text box can't be named the same.

Oh, and you might want to use the NZ function to deal with nulls:

=SUM(NZ(SumOfTicketCosts,0))

 

[BlueChicken]

Make sure that the original text box is not named the same (change it to txtSumOfTicketCosts and then make sure that all of your sums are referencing the FIELD - SumOfTicketCosts).

You basically have the same text box with the same formula but placed in each footer and it will do the trick but the name of the text box can't be named the same.

All the text boxes are named differently as I said before, and even with the formula you just gave me:

=SUM(NZ(SumOfTicketCosts,0))

I still am getting "#Error".

 

[SOS]

Click on the text box SumOfTicketCost in the DETAIL section. What is the name of the text box there?

 

[BlueChicken]

'SumOfTicketCost'

But before you tell me about your's having a 's' on the end, I already noticed that and removed it before checking if it worked.

 

[SOS]

'SumOfTicketCost'

But before you tell me about your's having a 's' on the end, I already noticed that and removed it before checking if it worked.

Now, didn't I say that it could not be named SumOfTicketCost? It should be changed to txtSumOfTicketCost (and the other ones have to have different names than that).

 

[BlueChicken]

OK, wait. The names of the three text boxes are:
Detail: 'SumOfTicketCost'
RequestIDFooter: 'TripCostSUM'
PageFooter: 'TotalTicketCosts'

The Control Source for each text box is:
Detail: 'SumOfTicketCost'
RequestIDFooter: '=Sum([SumOfTicketCost])'
PageFooter: '=Sum(NZ([SumOfTicketCost],0))'

 

[BlueChicken]

The names of the three text boxes are:
Detail: 'SumOfTicketCost'

This one is calculated by a query. I cannot change it without messing up my query.

 

[SOS]

Okay, do this:

Make the names of the three text boxes:
Detail: 'txtSumOfTicketCost'
RequestIDFooter: 'txtTripCostSUM'
PageFooter: 'txtTotalTicketCosts'

The Control Source for each text box is:
Detail: 'SumOfTicketCost'
RequestIDFooter: '=Sum(Nz([SumOfTicketCost],0))'
PageFooter: '=Sum(NZ([SumOfTicketCost],0))'

 

[SOS]

This one is calculated by a query. I cannot change it without messing up my query.

you change the NAME of the TEXT BOX, NOT the bound field.

 

[BlueChicken]

OK... made all of those changes... and while the Detail and RequestIDFooter ones still work, the bottom one; ReportFooter, is still saying #Error

 

[SOS]

Without being able to play with it, I'm not sure what is happening. I've exhausted my ideas. It should work if on the report footer. Not sure about the page footer though.

 

[SOS]

Actually, make sure that the bottom one didn't change on you to be looking for txtSumOfTicketCost (Auto Corrupt, I mean Auto Correct, if on could have done that). If it did, change it back to just =Sum(Nz([SumOfTicketCost],0)) instead.

 

[BlueChicken]

It should work if on the report footer. Not sure about the page footer though.

The report footer is not visible... not sure why. Even when I move it down there.

 

[BlueChicken]

The report footer is not visible... not sure why. Even when I move it down there.

Wait... I think it did, but in a really REALLY odd place...

 

[BlueChicken]

Wait... I think it did, but in a really REALLY odd place...

Ok, putting it in the Report Footer made it work, but it confused me when it showed up above the page footer... considering the report footer is below the page footer in design view.

Thanks again for all your help! Seems like you are the only one who ever helps me.

 

[SOS]

Seems like you are the only one who ever helps me.

It is just coincidence on that one

Good luck on the rest of the project.