?
Solved

MS Access 2013

Posted on 2014-07-30
6
Medium Priority
?
232 Views
Last Modified: 2014-08-01
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
0
Comment
Question by:dawber39
6 Comments
 

Author Comment

by:dawber39
ID: 40230727
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40230910
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40231169
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Expert Comment

by:PatHartman
ID: 40231982
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
 

Author Closing Comment

by:dawber39
ID: 40234907
Thank you - you people are awesome as always
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40235019
You are welcome!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question