calculations not right....

[stephendeloach via AccessMonster.com]

in my report i have Qty, UP2, and NetAmount. NetAmount =[Qty]*[UP2]

my [Qty] is = 5
my [UP2] is = 170.86

now when my NetAmount comes up is says 854.32, when it should be 854.30.......
why is this? I have [UP2] format set to $#,##0.00;($#,##0.00); with decimal
places set to 2. NetAmount =Nz([Qty],0)*Nz([UP2],0) Currency, Decimal set
to 2..... I cant figure this out...

 

[Jeff Boyce]

What "format" you are using has little/nothing to do with how the data is
stored.

What are the underlying data types of both (I only saw "Currency" for ?the
answer, ?one, ?both)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[stephendeloach via AccessMonster.com]

what do you mean? in the tables how is it stored? [Qty] - Number - Long
Integer - General Number - 2 decimal places. [UP2] - Currency -
Currency - 2 decimal. [NetAmount] - Curency - Currency - 2 decimal.....
hope this is what you are asking... thanks

What "format" you are using has little/nothing to do with how the data is
stored.

What are the underlying data types of both (I only saw "Currency" for ?the
answer, ?one, ?both)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

in my report i have Qty, UP2, and NetAmount. NetAmount =[Qty]*[UP2]

[quoted text clipped - 8 lines]

set
to 2..... I cant figure this out...

 

[Jeff Boyce]

?You are storing the [NetAmount] in the table? Isn't that something that
you are trying to calculate from the other two? How do you keep it
"synchronized" if one of the other two changes?

I'm a little confused...

Are you working in a form, a report, or the table? Do you use a query to
get your "answer"? How is the [NetAmount] formatted in the query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what do you mean? in the tables how is it stored? [Qty] - Number - Long
Integer - General Number - 2 decimal places. [UP2] - Currency -
Currency - 2 decimal. [NetAmount] - Curency - Currency - 2
decimal.....
hope this is what you are asking... thanks

What "format" you are using has little/nothing to do with how the data is
stored.

What are the underlying data types of both (I only saw "Currency" for ?the
answer, ?one, ?both)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

in my report i have Qty, UP2, and NetAmount. NetAmount =[Qty]*[UP2]

[quoted text clipped - 8 lines]

set
to 2..... I cant figure this out...

 

[stephendeloach via AccessMonster.com]

What I do is (in the form) enter the UnitPrice and Quantity, I have a check
box that when checked, adds 10% to the UnitPrice which is then entered in the
NetAmount field (in the form). I then go to the Report, double click the
report, it then Queries the information by Invoice#. I type in the Invoice#,
the report comes up with all the information I need for the Invoice#.
Everything works fine except the calculations..... The report gets the
UnitPrice and Quantity directly from the form. The NetAmount (in the Report)
is =Nz([Quantity],0)*Nz([UnitPrice],0) is there a way I can email you a
copy of the db and let you look at it? Thanks


Stephen

?You are storing the [NetAmount] in the table? Isn't that something that
you are trying to calculate from the other two? How do you keep it
"synchronized" if one of the other two changes?

I'm a little confused...

Are you working in a form, a report, or the table? Do you use a query to
get your "answer"? How is the [NetAmount] formatted in the query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what do you mean? in the tables how is it stored? [Qty] - Number - Long
Integer - General Number - 2 decimal places. [UP2] - Currency -

[quoted text clipped - 18 lines]

 

[Jeff Boyce]

If you have a table with the [UnitPrice] and [Quantity] stored, why does
your report "get the UnitPrice and Quantity directly from the form? This is
a most unusual design. Usually, you'd use a query that retrieves the
record(s) from the tables to provide these values.

Since most of the folks who help out here (including me) are volunteering
their time and tend to also practice "safe computing", you may not find
folks with either the time or willingness to accept an unknown file from an
unknown poster.

Besides, that would take the discussion out of the newsgroup. Some day,
other folks may have the same issue you are facing -- what starts in the
newsgroups stays in the newsgroups, that all might benefit.

You've been describing "how" you are doing something. Let's step back for a
second ... are you saying you'd like a way to show a single Invoice#'s
record/values (including calculated value(s)) in a report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

What I do is (in the form) enter the UnitPrice and Quantity, I have a
check
box that when checked, adds 10% to the UnitPrice which is then entered in
the
NetAmount field (in the form). I then go to the Report, double click the
report, it then Queries the information by Invoice#. I type in the
Invoice#,
the report comes up with all the information I need for the Invoice#.
Everything works fine except the calculations..... The report gets the
UnitPrice and Quantity directly from the form. The NetAmount (in the
Report)
is =Nz([Quantity],0)*Nz([UnitPrice],0) is there a way I can email you a
copy of the db and let you look at it? Thanks


Stephen

?You are storing the [NetAmount] in the table? Isn't that something that
you are trying to calculate from the other two? How do you keep it
"synchronized" if one of the other two changes?

I'm a little confused...

Are you working in a form, a report, or the table? Do you use a query to
get your "answer"? How is the [NetAmount] formatted in the query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

what do you mean? in the tables how is it stored? [Qty] - Number - Long
Integer - General Number - 2 decimal places. [UP2] - Currency -

[quoted text clipped - 18 lines]

set
to 2..... I cant figure this out...

 

[stephendeloach via AccessMonster.com]

Yes. I am running a query to get all the info on one invoice#. I run the
report which then runs the query for whatever invoice# i type in.. If this
makes any sense or helps any?

If you have a table with the [UnitPrice] and [Quantity] stored, why does
your report "get the UnitPrice and Quantity directly from the form? This is
a most unusual design. Usually, you'd use a query that retrieves the
record(s) from the tables to provide these values.

Since most of the folks who help out here (including me) are volunteering
their time and tend to also practice "safe computing", you may not find
folks with either the time or willingness to accept an unknown file from an
unknown poster.

Besides, that would take the discussion out of the newsgroup. Some day,
other folks may have the same issue you are facing -- what starts in the
newsgroups stays in the newsgroups, that all might benefit.

You've been describing "how" you are doing something. Let's step back for a
second ... are you saying you'd like a way to show a single Invoice#'s
record/values (including calculated value(s)) in a report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

What I do is (in the form) enter the UnitPrice and Quantity, I have a
check

[quoted text clipped - 31 lines]

 

[Jeff Boyce]

So, where all do you have these values, and where are you setting the
properties and where are you attempting the multiplication?

If this were mine, I'd store [UnitPrice] and [Quantity] in the table, create
a query that returns them and their product, and create a report based on
the query, with the formatting set in the report's textboxes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes. I am running a query to get all the info on one invoice#. I run the
report which then runs the query for whatever invoice# i type in.. If this
makes any sense or helps any?

If you have a table with the [UnitPrice] and [Quantity] stored, why does
your report "get the UnitPrice and Quantity directly from the form? This
is
a most unusual design. Usually, you'd use a query that retrieves the
record(s) from the tables to provide these values.

Since most of the folks who help out here (including me) are volunteering
their time and tend to also practice "safe computing", you may not find
folks with either the time or willingness to accept an unknown file from
an
unknown poster.

Besides, that would take the discussion out of the newsgroup. Some day,
other folks may have the same issue you are facing -- what starts in the
newsgroups stays in the newsgroups, that all might benefit.

You've been describing "how" you are doing something. Let's step back for
a
second ... are you saying you'd like a way to show a single Invoice#'s
record/values (including calculated value(s)) in a report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

What I do is (in the form) enter the UnitPrice and Quantity, I have a
check

[quoted text clipped - 31 lines]

set
to 2..... I cant figure this out...

 

[stephendeloach via AccessMonster.com]

[UnitPrice], [Quantity] and [NetAmount] are stored in the table. I then have
a query that returns the information I need to run the Report. I run the
Report and in the [UnitPrice] box its control source is set to UnitPrice, in
the [Quantity] box its control source is set to Quantity. I then have the
[NetAmount] and its control source is =Nz([Quantity],0)*Nz([UnitPrice],0) ...
. Which is showing 5 * 170.86 = 854.32 when it needs to be 854.30 Thanks
for the help..

So, where all do you have these values, and where are you setting the
properties and where are you attempting the multiplication?

If this were mine, I'd store [UnitPrice] and [Quantity] in the table, create
a query that returns them and their product, and create a report based on
the query, with the formatting set in the report's textboxes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes. I am running a query to get all the info on one invoice#. I run the
report which then runs the query for whatever invoice# i type in.. If this

[quoted text clipped - 31 lines]

 

[Jeff Boyce]

If you've already calculated the [NetAmount] and stored it in the table, why
are you re-calculating it in the report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

[UnitPrice], [Quantity] and [NetAmount] are stored in the table. I then
have
a query that returns the information I need to run the Report. I run the
Report and in the [UnitPrice] box its control source is set to UnitPrice,
in
the [Quantity] box its control source is set to Quantity. I then have the
[NetAmount] and its control source is =Nz([Quantity],0)*Nz([UnitPrice],0)
...
Which is showing 5 * 170.86 = 854.32 when it needs to be 854.30 Thanks
for the help..

So, where all do you have these values, and where are you setting the
properties and where are you attempting the multiplication?

If this were mine, I'd store [UnitPrice] and [Quantity] in the table,
create
a query that returns them and their product, and create a report based on
the query, with the formatting set in the report's textboxes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes. I am running a query to get all the info on one invoice#. I run the
report which then runs the query for whatever invoice# i type in.. If
this

[quoted text clipped - 31 lines]

set
to 2..... I cant figure this out...

 

[stephendeloach via AccessMonster.com]

The NetAmount in the report and in the table are different netamounts.. the
NetAmount in the table is UnitPrice * 10% - the netamount in the report is
[unitprice] (unitprice *10%) * [quantity] i know its not the best way to
setup a db but i was VERY new to access when i built this...

If you've already calculated the [NetAmount] and stored it in the table, why
are you re-calculating it in the report?

Regards

Jeff Boyce
Microsoft Office/Access MVP

[UnitPrice], [Quantity] and [NetAmount] are stored in the table. I then
have

[quoted text clipped - 26 lines]

 

[stephendeloach via AccessMonster.com]

The thing is.. in the Report, why isnt it multiplying 5*170.86 the correct
way?

The NetAmount in the report and in the table are different netamounts.. the
NetAmount in the table is UnitPrice * 10% - the netamount in the report is
[unitprice] (unitprice *10%) * [quantity] i know its not the best way to
setup a db but i was VERY new to access when i built this...

If you've already calculated the [NetAmount] and stored it in the table, why
are you re-calculating it in the report?

[quoted text clipped - 9 lines]

 

[Jeff Boyce]

When I start seeing "funky" results, I tend to throw out the old and start
over.

I don't know why.

Perhaps one of the other newsgroup readers has an idea what might be
happening...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

The thing is.. in the Report, why isnt it multiplying 5*170.86 the correct
way?

The NetAmount in the report and in the table are different netamounts..
the
NetAmount in the table is UnitPrice * 10% - the netamount in the report
is
[unitprice] (unitprice *10%) * [quantity] i know its not the best way
to
setup a db but i was VERY new to access when i built this...

If you've already calculated the [NetAmount] and stored it in the table,
why
are you re-calculating it in the report?

[quoted text clipped - 9 lines]

set
to 2..... I cant figure this out...