Percentages...gone awry.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple form that won't calculate anything I put in as expressions
(another story) but i also have a few precentage fields. I try to put in 0.3
and it goes back to 0.00%. I put in 0.63 and it returns 100.00%?!?!?! I'm so
donfangled mad I can spit nails. W...T...F...? AAARRRGGGHHH!!!!!! Oh, and I
HAVE to use #%^@ Access, that's what my boss wants. I have it working
wonderfully in Excel.

Sample of my work from Excel:

% OH 20% % Labor Markup 50%
% EQ Markup 30% Labor Cost/Hour $40.00
EQ Cost $545,000.00 Labor Hours 4000

EQ Sell $763,000.00 Labor Rate $60.00
Labor Cost $160,000.00
EQ OH $163,500.00 Labor Sell $240,000.00
EQ Profit $54,500.00
% EQ Profit 10% Labor OH $48,000.00
Labor Profit $32,000.00
% EQ Commission 13% % Labor Profit 20%
EQ Commission $7,085.00
% Labor Commission 13%
Labor Commission $4,160.00
Company Profit $75,255.00
Sales Commission $11,245.00 Sell Price $1,003,000.00
 
Did you forget your medication this morning?
We can get the expressions working later, first, let's concentrate on the
controls (Forms don't have fields, only tables and queries have fields). I
am assuming you are using text box controls on your form.
Open your form in design view. Right Click on a control you want to show as
percentages. Select the Format property. One of the options is percentage.
Also, if you want a fixed number of decimal places, set that in the Decimal
Places property.

Let me know if this helps, then we can get on to the expressions.

BTW, not a great entry to curse the product for which you want help. Most
of us here actually like Access.
 
jsc3489 said:
I have a simple form that won't calculate anything I put in as
expressions (another story) but i also have a few precentage fields.
I try to put in 0.3 and it goes back to 0.00%. I put in 0.63 and it
returns 100.00%?!?!?! I'm so donfangled mad I can spit nails.
W...T...F...? AAARRRGGGHHH!!!!!! Oh, and I HAVE to use #%^@ Access,
that's what my boss wants. I have it working wonderfully in Excel.
Click to expand...

From the sound of it, you've defined your data fields as one of the
integer types; probably Long Integer, but maybe Integer or even Byte.
Integers have no decimal places, by definition, so if you put in 0.3 it
rounds to 0, and if you put in .63 it rounds to 1 (100%).

Change the Field Size property for those fields to Single or Double,
depending on what sort of precision you need.

Access will treat you very nicely if you just tell her what you want.
 
fields and records from those record sources

Fields from Record Sources --- get it?

You are obviously an insufferable jerk. You ask for help, then get cranky
when someone tries to help. You will not learn much that way.

I think I will spend my time helping someone who can benefit from it.
 
Mr. "Klatuu"

My suggestion to you is to be a good example of those who may not know. For
instance, Mr. "Dirk Goldgar", did not inform me of my lack of "medication
this morning" as an insult, rather he went straight to the point and gave a
suggestion without pointing out my absence of knowledge that "forms don't
have fields". It will do you well to ignore the "ignorance of terms" and just
ANSWER THE QUESTION as best you know. If you feel the need to correct someone
as so they don't "post" thier "term inadequacies", then do so TACTFULLY, NOT
WITH MALICE. You will find people to more polite and receptive to your
suggestions. My two cents.

BU77|-|0L3
 
Well wadda ya know, that worked. Thank you so much for your help.

To the second part, how do you make expressions work?
 
jsc3489 said:
Well wadda ya know, that worked. Thank you so much for your help.

To the second part, how do you make expressions work?
Click to expand...

I'm afraid your question is too vague for me to answer. Please amplify
it a bit.
 
Ok. I figured out how to put (ex. =[EQ cost]*[EQ OH %]) expressions in my
form boxes to perform my calcs. My problem now is my code for two of my
boxes. I have created a table (of sorts) in code and I'm not quite sure how
to implement. My code was taken from my Excel sheet and converted (somewhat)
to work in Access. Here is the code:

Option Compare Database
------------------------------------------------------------------------------
Private Sub EQ_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.EQ_Profit_%.Value >= 0.445 Then
Form_Sales.EQ_Commission_%.Value = 0.21
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.345 Then
Form_Sales.EQ_Commission_%.Value = 0.2
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.295 Then
Form_Sales.EQ_Commission_%.Value = 0.19
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.245 Then
Form_Sales.EQ_Commission_%.Value = 0.18
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.195 Then
Form_Sales.EQ_Commission_%.Value = 0.17
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.145 Then
Form_Sales.EQ_Commission_%.Value = 0.15
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.095 Then
Form_Sales.EQ_Commission_%.Value = 0.13
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.045 Then
Form_Sales.EQ_Commission_%.Value = 0.1
Else
Form_Sales.EQ_Commission_%.Value = 0.05
End If
End Sub
-------------------------------------------------------------------------------
Private Sub Labor_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.Labor_OH_%.Value >= 0.945 Then
Form_Sales.Labor_Commission_%.Value = 0.21
ElseIf Form_Sales.Labor_OH_%.Value >= 0.825 Then
Form_Sales.Labor_Commission_%.Value = 0.2
ElseIf Form_Sales.Labor_OH_%.Value >= 0.695 Then
Form_Sales.Labor_Commission_%.Value = 0.19
ElseIf Form_Sales.Labor_OH_%.Value >= 0.575 Then
Form_Sales.Labor_Commission_%.Value = 0.18
ElseIf Form_Sales.Labor_OH_%.Value >= 0.445 Then
Form_Sales.Labor_Commission_%.Value = 0.17
ElseIf Form_Sales.Labor_OH_%.Value >= 0.325 Then
Form_Sales.Labor_Commission_%.Value = 0.15
ElseIf Form_Sales.Labor_OH_%.Value >= 0.195 Then
Form_Sales.Labor_Commission_%.Value = 0.13
ElseIf Form_Sales.Labor_OH_%.Value >= 0.075 Then
Form_Sales.Labor_Commission_%.Value = 0.1
Else
Form_Sales.Labor_Commission_%.Value = 0.05
End If
End Sub
 
jsc3489 said:
Ok. I figured out how to put (ex. =[EQ cost]*[EQ OH %]) expressions
in my form boxes to perform my calcs. My problem now is my code for
two of my boxes. I have created a table (of sorts) in code and I'm
not quite sure how to implement. My code was taken from my Excel
sheet and converted (somewhat) to work in Access. Here is the code:

Option Compare Database
---------------------------------------------------------------------- --------
Private Sub EQ_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.EQ_Profit_%.Value >= 0.445 Then
Form_Sales.EQ_Commission_%.Value = 0.21
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.345 Then
Form_Sales.EQ_Commission_%.Value = 0.2
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.295 Then
Form_Sales.EQ_Commission_%.Value = 0.19
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.245 Then
Form_Sales.EQ_Commission_%.Value = 0.18
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.195 Then
Form_Sales.EQ_Commission_%.Value = 0.17
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.145 Then
Form_Sales.EQ_Commission_%.Value = 0.15
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.095 Then
Form_Sales.EQ_Commission_%.Value = 0.13
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.045 Then
Form_Sales.EQ_Commission_%.Value = 0.1
Else
Form_Sales.EQ_Commission_%.Value = 0.05
End If
End Sub
---------------------------------------------------------------------- ---------
Private Sub Labor_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.Labor_OH_%.Value >= 0.945 Then
Form_Sales.Labor_Commission_%.Value = 0.21
ElseIf Form_Sales.Labor_OH_%.Value >= 0.825 Then
Form_Sales.Labor_Commission_%.Value = 0.2
ElseIf Form_Sales.Labor_OH_%.Value >= 0.695 Then
Form_Sales.Labor_Commission_%.Value = 0.19
ElseIf Form_Sales.Labor_OH_%.Value >= 0.575 Then
Form_Sales.Labor_Commission_%.Value = 0.18
ElseIf Form_Sales.Labor_OH_%.Value >= 0.445 Then
Form_Sales.Labor_Commission_%.Value = 0.17
ElseIf Form_Sales.Labor_OH_%.Value >= 0.325 Then
Form_Sales.Labor_Commission_%.Value = 0.15
ElseIf Form_Sales.Labor_OH_%.Value >= 0.195 Then
Form_Sales.Labor_Commission_%.Value = 0.13
ElseIf Form_Sales.Labor_OH_%.Value >= 0.075 Then
Form_Sales.Labor_Commission_%.Value = 0.1
Else
Form_Sales.Labor_Commission_%.Value = 0.05
End If
End Sub
---------------------------------------------------------------------- ---
In some of the form boxes my expressions work, in other I get #Name?.
I think it's because of my code is not being utilized properly or
it's bad code.
Click to expand...

There are problems with the code, but whether that's why you're getting
the #Name? error is not clear to me. That generally means that an
expression contains a name -- the name of a control or function,
usually -- that Access can't resolve. One common cause of this is a
circular reference: a control contains an expression that refers to
itself, or to another control that contains an expression that refers to
the first control, etc. Or, of course, you could have simply misspelled
the name of the control, or, since your control names contain invalid
characters (spaces, the percent sign), you may have failed to quote it
properly with square brackets ([]).

Looking at your code above, I'm not sure what you're trying to do, but
I'm pretty sure you'd do better to use the AfterUpdate event of each
control, rather than the BeforeUpdate event -- the BeforeUpdate event is
mainly for validation of the value entered by the user. Are these
controls whose values you're calculating, [EQ Commission %] and [Labor
Commission %], going to be manually updatable after you've calculated
their initial values here? If so, they must not be calculated controls
(that is, their ControlSource properties must not begin with an equals
sign), but other than that your approach is correct. Your code could
use some adjusting, though:

'----- start of revised code ------

Option Compare Database
Option Explicit

Private Sub EQ_Commission___AfterUpdate()

Select Case Me![EQ Profit %].Value
Case Is >= 0.445
Me![EQ Commission %] = 0.21
Case Is >= 0.345
Me![EQ Commission %] = 0.2
Case Is >= 0.295
Me![EQ Commission %] = 0.19
Case Is >= 0.245
Me![EQ Commission %] = 0.18
Case Is >= 0.195
Me![EQ Commission %] = 0.17
Case Is >= 0.145
Me![EQ Commission %] = 0.15
Case Is >= 0.095
Me![EQ Commission %] = 0.13
Case Is >= 0.045
Me![EQ Commission %] = 0.1
Case Else
Me![EQ Commission %] = 0.05
End Select

End Sub

' ... and similar for Labor_Commission___AfterUpdate()

'----- end of revised code -----

Note, by the way, the line "Option Explicit" near the top of the module.
You should have that, so that the VB Editor can tell if you've
misspelled a variable or control name. You can tell the VB Editor to
add that for all new modules you create, by checking the "Require
variable declaration" option in the Options dialog of the VB Editor.
 
My code reflects a table as such:

Parts Labor Commission
------------------------------------------------
1%-4% | 1%- 7% | 5%
5%-9% | 8%-19% | 10%
etc.

I've implemented the new code, but I still get 0% as a result in my "EQ
Commission %" and "Labor Commission %" boxes. Do you think that the % in the
name is throwing things off?
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!


Dirk Goldgar said:
jsc3489 said:
Ok. I figured out how to put (ex. =[EQ cost]*[EQ OH %]) expressions
in my form boxes to perform my calcs. My problem now is my code for
two of my boxes. I have created a table (of sorts) in code and I'm
not quite sure how to implement. My code was taken from my Excel
sheet and converted (somewhat) to work in Access. Here is the code:

Option Compare Database
---------------------------------------------------------------------- --------
Private Sub EQ_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.EQ_Profit_%.Value >= 0.445 Then
Form_Sales.EQ_Commission_%.Value = 0.21
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.345 Then
Form_Sales.EQ_Commission_%.Value = 0.2
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.295 Then
Form_Sales.EQ_Commission_%.Value = 0.19
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.245 Then
Form_Sales.EQ_Commission_%.Value = 0.18
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.195 Then
Form_Sales.EQ_Commission_%.Value = 0.17
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.145 Then
Form_Sales.EQ_Commission_%.Value = 0.15
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.095 Then
Form_Sales.EQ_Commission_%.Value = 0.13
ElseIf Form_Sales.EQ_Profit_%.Value >= 0.045 Then
Form_Sales.EQ_Commission_%.Value = 0.1
Else
Form_Sales.EQ_Commission_%.Value = 0.05
End If
End Sub
---------------------------------------------------------------------- ---------
Private Sub Labor_Commission___BeforeUpdate(Cancel As Integer)
If Form_Sales.Labor_OH_%.Value >= 0.945 Then
Form_Sales.Labor_Commission_%.Value = 0.21
ElseIf Form_Sales.Labor_OH_%.Value >= 0.825 Then
Form_Sales.Labor_Commission_%.Value = 0.2
ElseIf Form_Sales.Labor_OH_%.Value >= 0.695 Then
Form_Sales.Labor_Commission_%.Value = 0.19
ElseIf Form_Sales.Labor_OH_%.Value >= 0.575 Then
Form_Sales.Labor_Commission_%.Value = 0.18
ElseIf Form_Sales.Labor_OH_%.Value >= 0.445 Then
Form_Sales.Labor_Commission_%.Value = 0.17
ElseIf Form_Sales.Labor_OH_%.Value >= 0.325 Then
Form_Sales.Labor_Commission_%.Value = 0.15
ElseIf Form_Sales.Labor_OH_%.Value >= 0.195 Then
Form_Sales.Labor_Commission_%.Value = 0.13
ElseIf Form_Sales.Labor_OH_%.Value >= 0.075 Then
Form_Sales.Labor_Commission_%.Value = 0.1
Else
Form_Sales.Labor_Commission_%.Value = 0.05
End If
End Sub
---------------------------------------------------------------------- ---
In some of the form boxes my expressions work, in other I get #Name?.
I think it's because of my code is not being utilized properly or
it's bad code.
Click to expand...

There are problems with the code, but whether that's why you're getting
the #Name? error is not clear to me. That generally means that an
expression contains a name -- the name of a control or function,
usually -- that Access can't resolve. One common cause of this is a
circular reference: a control contains an expression that refers to
itself, or to another control that contains an expression that refers to
the first control, etc. Or, of course, you could have simply misspelled
the name of the control, or, since your control names contain invalid
characters (spaces, the percent sign), you may have failed to quote it
properly with square brackets ([]).

Looking at your code above, I'm not sure what you're trying to do, but
I'm pretty sure you'd do better to use the AfterUpdate event of each
control, rather than the BeforeUpdate event -- the BeforeUpdate event is
mainly for validation of the value entered by the user. Are these
controls whose values you're calculating, [EQ Commission %] and [Labor
Commission %], going to be manually updatable after you've calculated
their initial values here? If so, they must not be calculated controls
(that is, their ControlSource properties must not begin with an equals
sign), but other than that your approach is correct. Your code could
use some adjusting, though:

'----- start of revised code ------

Option Compare Database
Option Explicit

Private Sub EQ_Commission___AfterUpdate()

Select Case Me![EQ Profit %].Value
Case Is >= 0.445
Me![EQ Commission %] = 0.21
Case Is >= 0.345
Me![EQ Commission %] = 0.2
Case Is >= 0.295
Me![EQ Commission %] = 0.19
Case Is >= 0.245
Me![EQ Commission %] = 0.18
Case Is >= 0.195
Me![EQ Commission %] = 0.17
Case Is >= 0.145
Me![EQ Commission %] = 0.15
Case Is >= 0.095
Me![EQ Commission %] = 0.13
Case Is >= 0.045
Me![EQ Commission %] = 0.1
Case Else
Me![EQ Commission %] = 0.05
End Select

End Sub

' ... and similar for Labor_Commission___AfterUpdate()

'----- end of revised code -----

Note, by the way, the line "Option Explicit" near the top of the module.
You should have that, so that the VB Editor can tell if you've
misspelled a variable or control name. You can tell the VB Editor to
add that for all new modules you create, by checking the "Require
variable declaration" option in the Options dialog of the VB Editor.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
From the ranting in your original post, I should have guessed you have no
sense of humor. You come in cussing and screaming and denegrating Access,
yet you want help with it. Then when offered some guidance beyond the
question, you get all warped out of shape.
Perhaps in the link from Microsoft you posted, they make references to
fields; however, to be sure we are talking about the same objects, it helps
if we use the same terminology. Having had experience in many different
database environments, I can tell you that fields refer to properties of a
recordset.

I stand by my analysis - you, sir, are a jerk.
 
As I am a jerk, since you have named me so , I will dispell some much needed
teachings in your direction. If a person states he or she is "angry" or "mad"
then it not be wise to invoke "humorous antics" at said person. For example,
when my wife is ill of month, she politley tells to me "Don't poke the bear!"
and I heed this warning, for the outcome will not be fair if I stray, so I
stay clear and approach with caution.

So, in short, don't poke the bear.
 
jsc3489 said:
My code reflects a table as such:

Parts Labor Commission
------------------------------------------------
1%-4% | 1%- 7% | 5%
5%-9% | 8%-19% | 10%
etc.

I've implemented the new code, but I still get 0% as a result in my
"EQ Commission %" and "Labor Commission %" boxes. Do you think that
the % in the name is throwing things off?
Click to expand...

If the names I guessed at in my sample code are correct, I don't think
so. But are those text boxes also bound to Long Integer or Integer
fields? If so, then they, too, must have their underlying fields
changed to a Field Size that can store fractions.
 
Finally. I have it 99% figured out. I had a wrong name in my code. :P I could
never get the AfterUpdate or BeforeUpdate to work right so I used Mouse
instead. I just "hover" my mouse over the boxes and it works great. But, when
I go to the next record and since there isn't any data entered, if I
accidentally hover my mouse, it tries to calculate and I get an error. So
now, I'm trying to figure a way to error trap. Or, I thought about a
"calculate" (Update) button on the form (would need great help on the code
for that one).

The two boxes (EQ Com. % and Labor Com. %) that house the "refence" to my
code are never touched. In fact, I have access blocked on them both as an
accidental change causes errors.

Basically I'd like it to calculate as Excel does, but I know Access can't do
that. What do you suggest as the next best thing?
 
110% done. I managed to create my "Update" button with very little effort. I
want thank you Dirk for all your help.

I would also like to apoligize to Klatuu for my jerk-like behavior and wish
him well.
 
Back
Top