473,836 Members | 1,520 Online

# Calculation problem in Access 2000

Hi!

Recently we, in Norway changed to three different VAT levels. All three
needs to be on the invoice program. This is easy obtained via a new
field and set the rowsource to valuelist and make the three values :
0;0,00;0,06;0,1 2;0,25.

However my problem is calculating the orderlines and make them be
summed in a textbox in the footer of the frmOrderlines.

Can anyone see what I'm surely missing?

=Sm((Nz([Price]*[Amount]*[Discount])+[GeneralTaxes]))*[VTA]

which I thought would produce a result.

Hope anybody can help me.

Me.Name

Mar 22 '06 #1
6 1683
Apart from some typos - like sm for sum and probably VTA for VAT....

Are any of the fields null (Price/Amount/Discount.....et c

If they are (or can be) then the nz function looks in the wrong place.

=Sum(((nz(Price ,0)*nz(Amount,0 )*(nz(Discount, 0)) +
nz(GeneralTaxes ,0))*VAT)

mind you I think discount should be (1 - nz(Discount,0))

Mar 24 '06 #2
No nulls, and I already use the NZ() function on all fields.
And the line should really be (as sum of each line):

=Nz([Price]+[GeneralTaxes])*Nz([Amount])*Nz(((1-[Discount])/100)*100)

and this works fine for the sum of each orderline

However, adding =Sum to it in the footer in a textbox suddenly just
stopped to work.

Could it be that I'm working on an access 2000 db in office xp, win xp
pro?

Because it just stopped to function!

Anyway, here's the calculation for a textbox in the footer of
frmOrderdetails :

=Sum(Nz(Price*A mount)) in the ControlSource property.

Botn fields are set correctly as moneyvalue and integer value and have
been working nicely until I loaded this access 2000 db into access xp.

Have a nice day!

But why it doesn't return any value I can't tell

cjb_kjb skrev:
Apart from some typos - like sm for sum and probably VTA for VAT....

Are any of the fields null (Price/Amount/Discount.....et c

If they are (or can be) then the nz function looks in the wrong place.

=Sum(((nz(Price ,0)*nz(Amount,0 )*(nz(Discount, 0)) +
nz(GeneralTaxes ,0))*VAT)

mind you I think discount should be (1 - nz(Discount,0))

Mar 28 '06 #3
Hi!

I solved the problem.

I just made a new frmOrderdetail and now it works.

So what could have been wrong?...u tell me :-)

Me.Name

Mar 28 '06 #4

<gs***@hotmail. com> wrote in message
news:11******** *************@z 34g2000cwc.goog legroups.com...
Hi!

I solved the problem.

I just made a new frmOrderdetail and now it works.

So what could have been wrong?...u tell me :-)

Er, you needed to create a new frmOrderdetail? Probably the Amazing
Biodegradable Forms Feature, in one variation or another. That's sometimes
called the "white flash" (after a famous Candid Camera episode).
Mar 28 '06 #5
I have no idea why that solved the problem except it doesn't surprise
me.

I get the feeling Access has a few bugs and corrupts things
occasionally and no matter what you do it doesn't come right again
unless you recreate the object from scratch.

I find Access 2003 is worse for this than Access 2000.

One case that comes to mind. I have an application with several forms
that work in a similar way but with some differences eg Cheques,
payments, receipts etc. I had a combo box on one where the rowsource
was "Select ....Where ID = forms!FormA!Fie ldA".
I copied this combo box to another form and changed the select to
"Select....Wher e ID = forms!FormB!Fie ldA". Worked fine.
Then after I did some other changes on the form the combo box rowsource
reverted to the original query and of course wouldn't work. Changed it
again. Worked fine. Did some more changes to the form. Combo box
rowsource reverts to the original again. This same thing happened on
several forms.

Mar 29 '06 #6
"cjb_kjb" <cj*****@hotmai l.com> wrote

I find Access 2003 is worse for this than Access 2000.

With all three Service Packs and more recent patches applied, Access 2000 is
finally reasonably stable; when delivered, it was arguably the worst release
every of Access (and that's going some, if you remember Access 95).

Obviously our experience varies, because I have not found Access 2003 to be
_less_ stable than Access 2000. I do not like its default of "online
primary" help, but haven't found it to be unstable.

Larry Linson
Microsoft Access MVP
Mar 30 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.