MS Access 2013

I have a report that is based solely on an SQL statement in the record source. It is very efficient - however, there are certain needs that need to be addressed for one particular customer.

In the report there is a number of calculated fields that produce the total price of each number of items, and an invoice total. there is a textbox control that displays a customer number. it is designated as field79 in the property sheet with a control source of TrxCust.  there are several hidden textbox controls that calculate the invoice total. They are as follows

Hidden:

Control Sources
=rSetValue("CustInv","InvoiceTotal","Continues...") - Field 102
TrxAmount
=Sum([Amount])

Not hidden

=IIf(IsNumeric([InvoiceTotal]),"Total:","")  with one adjacent to that named "InvoiceTotal"

What I need to do is ad $20.00 to the InvoiceTotal when the field79 "TrxCust" is 27. There is no designated format for field 79 - so I cannot tell you if it is number or text - and Decimal places is set to Auto

I have tried different variations of code in the On Load property Like

If Me.Field79.Value = 27 Then
Me.InvoiceTotal.Value = InvoiceTotal + Text127.Value
End If
 With and without Value added - and I am lost - I've attached the SQL code that pulls the info for this Invoice report

Any help would be greatly appreciated
SQL.txt
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dawber39Database Analyst / Application DeveloperAuthor Commented:
I might add that there is a form to view this order that to is based on SQL - and the subform within it is based on a query that does not contain the Cust No.
0
Jeffrey CoachmanMIS LiasonCommented:
Without a sample file, and the explicit steps to recreate the desired result, ...I am at a loss here...

I am sure that what you are asking is as simple as you explain, ...but without any context, ...we would be just guessing till we hit on it...

A shot in the dark would be to try your code on the Format event of the section that contains these controls.

JeffCoachman
0
Gustav BrockCIOCommented:
This (guessing only) should do as controlsource:

=[InvoiceTotal]+IIf(Val([Field79])=27,[Text127],0)

You should rename your textboxes to something meaningful like txtSurcharge.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
I think Gustav probably solved your question but given that this is a calculated control, it is not being saved to the table.  So, if you actually need to record the surcharge, you'll need to give us a little more information.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Thank you - you people are awesome as always
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.