M Costumes
Member
- Local time
- Today, 08:54
- Joined
- Feb 9, 2021
- Messages
- 75
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
txtCleaningEstimate
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
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).
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
Code:
=IIf([ReciprocalLoan]=True,0,Sum([RentalRate])-Sum([RepairDiscount]+[OtherDiscount]))
Code:
=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
Code:
=IIf([ReciprocalLoan]=True,0,Sum([RentalRate])-Sum([RepairDiscount]+[OtherDiscount])*0.2)
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).