Calculated fields and syntax (1 Viewer)

[M Costumes]

I'm trying to create several calculated fields (textboxes) in the Report Footer. My report is based on a query. I'm essentially creating an invoice with sums of line items. I'm very new to SQL and all of this, so I think it's something in my syntax, but I can't get some of them to play nice. This is what I have:

Fields in Report Header:
[AdditionalWeeks] textbox
[ATCClean] checkbox
[ReciprocalLoan] checkbox

Fields in Details (all textboxes):
[RentalRate]
[RepairDiscount]
[OtherDiscount]

In the Report footer this is what I have that are working:
txtSubTotal

=IIf([ReciprocalLoan]=True,0,Sum([RentalRate])-Sum([RepairDiscount]+[OtherDiscount]))

txtCleaningEstimate

=IIf([ATCClean]=True,Count([InventoryID])*15,0)

These are the other two fields and what I want them to do, but I can't quite get them to play nice:
txtExtraWeekTotal = (the value of [txtSubTotal]*0.2) * the value of [ExtraWeeks]
I can get the first half working

=IIf([ReciprocalLoan]=True,0,Sum([RentalRate])-Sum([RepairDiscount]+[OtherDiscount])*0.2)

but I'm messing up when I try to add the second part: multiply by the value of [ExtraWeeks]

txtTotalEstimate = all the values that have been calculated added together
txtSubTotal + txtCleaningEstimate + txtExtraWeekTotal
My understanding is that to to do this, I need all the formulas together, I cannot use [textbox]. Which I get, but I'm having trouble getting the syntax correct--I'm getting really confused with parentheses.

Or is there a better way in general of doing this? Ultimately what I want to happen, is the user selects the rental from a form, clicks a button, and the query to generate the report runs & also opens the Report in print preview (so they never see the query table).

 

[theDBguy]

Hi. It's hard to follow what you're trying to describe without being able to see it in action. However, if you're trying to use the result of a previous calculation into a new calculation, try repeating the previous calculation in the new calculation. For example, if you were doing something like:

ItemTotal = Qty * Price

and then later try something like:

GrandTotal = Sum(ItemTotal)

and it doesn't work, try it this way:

GrandTotal = Sum(Qty * Price)

Hope that helps...

 

[plog]

...but I'm messing up when I try to add the second part: multiply by the value of [ExtraWeeks]

Think of your post like your report. You listed all these fields in the first half, then in the second half out of no where you mention this field called [ExtraWeeks]. Where'd that come from? Am I or the report suppose to know where that is?

Is that in the Report Header, the Details? Is it even a field in your query?

 

[M Costumes]

Found what I was doing wrong. I was trying to use Sum where it did not need to be used.
What's working for txtExtraWeeksTotal =[txtSubTotal]*0.2*[AdditionalWeeks]
I misunderstood what "Sum" actually means in this application....which I'm not surprised at all, I have trouble remembering when doing basic stuff in Excel too.

Thank you @theDBguy and @plog You two have answered so many of my newbie questions in the last couple weeks, and I really appreciate your insight and patience!

 

[theDBguy]

Found what I was doing wrong. I was trying to use Sum where it did not need to be used.
What's working for txtExtraWeeksTotal =[txtSubTotal]*0.2*[AdditionalWeeks]
I misunderstood what "Sum" actually means in this application....which I'm not surprised at all, I have trouble remembering when doing basic stuff in Excel too.

Thank you @theDBguy and @plog You two have answered so many of my newbie questions in the last couple weeks, and I really appreciate your insight and patience!

Hi. Glad to hear you got it sorted out. Good luck with your project.