% stuff not calc'ing correctly?

[r]

Hello-

I have two fields that are meant to work together to show both the dollar
amount and percent of a total budget.

I have the budget amount on the form, then the dollar and percent controls
on the form as well. When the user enters a value in the dollar field, the
percent field will be updated with the percent of the total budget that
dollar amount is. Conversely, is the percent is entered in the percent
control, the dollar amount of the total budget will display.

This is an example of what I expect:

Total Budget: $100


Dollars: $25
Percent: 25%

Dollars is calculated as percent * totalbudget.
Percent is calculated as dollars/totalbudget.

However....

When I enter $25 in the dollars field, the percent field is showing 0% - any
number less than $100 gives 0%, but when I enter 100 in the dollars field, I
get 100%.

What the heck am I doing wrong?! And can someone forsee a problem with my
formula for calculating the dollars based on what's above?

Thanks in advance.

-R

 

[Wayne Morgan]

What is the Control Source of these two textboxes? Are you doing the
calculation in the Control Source or in code? Please post the actual
calculation. What is the Format property of these two textboxes? If the
textboxes are bound to fields in a table, what are the data types and SIZE
of the fields?

 

[r]

Hi Wayne,

Sorry for delay in replying - was out for a few days. Hopefully you get
this!

The control source for each is a field in a table. I originally had them
formatted as currency and percentage, but also have tried them formatted as
just numeric, long integers. Couldn't tell a difference in the calculation.

I'm doing the calculation using macros. When a value is entered in the
Dollars field, a macro is run on the lost focus event and uses SetValue to
do the calculation and set the resulting value in the Percentage field.
Vice versa if a value is entered in the Percentage field instead.

Both fields were formatted as currency & percentage, but I also tried them
with no formatting at all. Couldn't tell a difference in the calculation.

I think that answers all the questions... right?

Thanks.

-R

 

[Wayne Morgan]

You say you've tried the fields "formatted" as Currency, Percentage, and
Long Integers. Was the formatting or field size? Formatting affects the
display only, field size affects the actual numbers. For a Number data type
in a table, the possible Field Sizes are Byte, Integer, Long Integer,
Single, Double, Replication ID, and Decimal. Another option is to set the
Data Type of the field to Currency instead of Number. For your calculations,
I recommend that the Data Type be set to Currency then set the Format (in
the bottom pane) as desired. The Currency data type will give you more
accurate results in calculations because of the way the number is handled.

Next, why is the source for each a field in the table? Since you can
calculate one from the other, store one and calculate the other as needed.
By storing both, you are duplicating the information stored.

 

[r]

Good point, Wayne - about not needing to store both values. Thanks. BTW,
fields are both long integers. I'll give it another try today, implementing
your suggestions. Thanks again!

 

[Wayne Morgan]

If the fields are long integers and you're trying to store values with
decimals, you won't get the right answers. You need a data type that will
accept decimals. Currency would be perfect for this.

 

[r]

Hm..

The fields I'm dealing with are in a subform that is set to Continuous.
When the field isn't bound to anything, ALL the percent fields get the
resulting answer of the calculation for the record being editted. Is there
a way around this??

Thanks.

-R

 

[Wayne Morgan]

Yes and no. You can do the calculation in the query feeding the subform.
This will create a calculated field in the query that you could then bind
the textbox to. However, the textbox won't be editable by the user.