Discussion Locked

Calculations in MS Access 2000

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm designing a database in MS Access 2000 to keep track of inventory and I can't get a calculation to to give me a correct answer. The calculation (On paper) is as follows.
In_Stock=Current
Current=In_Stock-Adjustment.
Any ideas on how is the best way to implement this? Every way I could think of always gives me the wrong answer(ie things like 20-5=10). Thanks. JJC

9 replies

by peter ritchie

The calculation as you've described it will work with Access.

Perhaps you can supply a bit more detail? For example, are you variables columns in a recordset, or are they variables within a form?

Nothing in your descriptions suggests why it would not work.

by shmaltz

I agree with answer one if the value of current is 20 and the value of adjustment is 5 then access IS NOT GIVING YOU FOR CURRENT 10.
That said here is how to troubleshoot this.
Put debug.print commands or msgbox commands to print the value of all Current, In_stock, and adjustment on every step of the program: e.i. when you adjust In_stock = current, you would do before and after:
debug.print "before current = " & current
debug.print "before In_stock = " & In_stock
In_stock=current
debug.print "after current = " & current
debug.print "after In_stock = " & In_stock
Now look in the debug window what the value is.
Since I dont know where current gets the value from maybe you are just wrong, b/c you assume that current should be 20 dosnt mean that it is.

by crosstrek1

Okay, here is more detail on the above problem. As part of a table I added the fields In_Stock, Adj & Current. When in the Form's design view under the proerties I set up Current to be SUM([In_Stock]-[Adj]). Then when testing the form I woulld enter a number in the In_Stock field and then another number in Adj and the answer it gives me is wrong. Any suggestions? Thanks

by seagull data services

I don't have a copy of Access2k at home but trying out your problem in Access97 the problem seems to be with using Sum()
What this is doing is adding up all of the values for [In_Stock]-[Adj] in all the records in the table. For Example, this is what I tried.

In_Stock Adj Current
Record 1 200 5 253
Record 2 30 10 253
Record 3 40 2 253
-------- --- --- ---
Totals: 270 - 17 = 253

I don't know what result you are looking for but, if you just want the value for the current record, leave out Sum(), i.e. the textbox ControlSource should be =[In_Stock]-[Adj]. If that's not what you want then tell us what you are trying to do please. 8o)
John

This discussion is closed to new replies.