By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,311 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,311 IT Pros & Developers. It's quick & easy.

How to see results in a table or report when using formula in a split form?

P: 1
Can any body help me?
I am using ms access 2007, i have a problem regarding formula.
My form:
Table Name: Cellphone
Fields Name : "Cellphone List" "Cost" "Actual Cost" "Difference" "Percentage"

I am using a split form."Difference" and "Percentage" fields have formula.

Expand|Select|Wrap|Line Numbers
  1. Control Source of Difference "=[Actual Cost]-[Cost]"
  2. Control Source of Percentage "=[Cost]/[Actual Cost]"
It really works fine i can saw all results in split form including TOTALS but my main problem was i can't see results of "Difference" and Percentage" on a table
or if i make a WIZARD REPORT only "Cellphone List" "Cost" and "Actual Cost" have values but "Difference" and "Percentage" fields i cant see any value.
I thinks it is because the 2 fields have an equation? I dont really know what's the problem? Please help me to solve it.Thanks in advance
Jan 16 '12 #1
Share this Question
Share on Google+
7 Replies


100+
P: 144
Assuming you are set up to protect the data from input on the form (diff & percent) you can set up the following Form OnCurrent event and leave the fields control source alone (reset it to the fieldname in the dropbox):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Me.diffence = Me.ActualCost - Me.Cost
  3. Me.percent = Me.Cost / Me.ActualCost
  4. End Sub
  5.  
The table will accept the calculation. Yu may have to run an update query accross the board or stroll through the records real quick to change the present data value. There are a few other ways using queries as your record source for the form. I'd rather manipulate it from the code.
Jan 16 '12 #2

100+
P: 144
Use the properties box for the whole "Form" in design mode, look at the Event tab, select the "..." next to "On Current" and insert the two inner lines above inside the subprocedure. Change the variable names I used, of course, to match your fieldnames. I hope this is clear for now.
Jan 16 '12 #3

NeoPa
Expert Mod 15k+
P: 31,707
Nicole05:
It really works fine i can saw all results in split form including TOTALS but my main problem was i can't see results of "Difference" and Percentage" on a table
Why would that surprise you? You have formulas on controls in your form that show you these values in the form. You have nothing in your table that does anything similar. Why would the fact that the information which is on the form, is missing from the table be cause for thinking anything is wrong?

PS. Updating fields in your table should never be recommended by anyone who understands Normalisation (See Database Normalisation and Table Structures.) or working with any RDBMS. You should think very carefully before considering taking that course in any database design.
Jan 16 '12 #4

100+
P: 144
I meant only update to fix the preexisting condition. After that no update would be necessary.
Jan 16 '12 #5

NeoPa
Expert Mod 15k+
P: 31,707
I'm not sure I follow clearly. I'm saying that [Difference] & [Percent] fields shouldn't even exist as they represent data that is already calculable from existing data. I didn't mean to be disparaging of your posts (as I've seen some of your good work), but Normalisation is such an important and fundamental issue that it needs to be highlighted wherever it comes up.
Jan 16 '12 #6

100+
P: 144
Quote from OP:
"but my main problem was i can't see results of "Difference" and Percentage" on a table."

The OP wants the results in a table, and didn't see making his calculations for other applications of his data somewhere down the road. Also, even if he uses my initial approach, he has some coding to do on the form controls (events) so that if he changes the value for Cost and Actual cost on the form, the Difference and Percentage controls would have to change with them.

I appreciate your mentorship.
Jan 16 '12 #7

NeoPa
Expert Mod 15k+
P: 31,707
Whenever I see any question that pre-supposes a non-normalised approach (as in this case), I feel it's my duty to enlighten. Not only for the benefit of the member asking the question, but also for all the others who read the thread over time. That is not to say I won't help them go the wrong way if they are insistent, that is their choice ultimately, but I always explore first their understanding. This also ensures that the warnings are there before anyone else thinks any non-normalised approach is a correct solution. Nearly always it's simply lack of awareness that leads them to think that way.
Jan 16 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.