% stuff not calc'ing correctly?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

r - 30 Jul 2005 00:22 GMT

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

Reply to this Message

Wayne Morgan - 30 Jul 2005 02:39 GMT

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?

Signature

Wayne Morgan
MS Access MVP

> Hello-
>
[quoted text clipped - 32 lines]
>
> -R

Reply to this Message

r - 03 Aug 2005 18:44 GMT

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

> 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
[quoted text clipped - 42 lines]
> >
> > -R

Reply to this Message

Wayne Morgan - 04 Aug 2005 11:53 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> Hi Wayne,
>
[quoted text clipped - 14 lines]
> 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.

Reply to this Message

r - 04 Aug 2005 17:07 GMT

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!

> You say you've tried the fields "formatted" as Currency, Percentage, and
> Long Integers. Was the formatting or field size? Formatting affects the
[quoted text clipped - 32 lines]
> > 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.

Reply to this Message

Wayne Morgan - 04 Aug 2005 18:27 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> 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!

Reply to this Message

r - 04 Aug 2005 19:15 GMT

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

> 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
[quoted text clipped - 9 lines]
> > implementing
> > your suggestions. Thanks again!

Reply to this Message

Wayne Morgan - 04 Aug 2005 21:30 GMT

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.

Signature

Wayne Morgan
MS Access MVP

> Hm..
>
[quoted text clipped - 3 lines]
> there
> a way around this??

Reply to this Message