In ACCESS...Can an equation be used as a value?

[Ovatvvon]

The Situation:
I have a table that is being used for some supply inventory. There is a field for how many of an item we have on hand currently, how many are on order right now, and how many we are supposed to have (On Hand, Order Level, and Optimum Stock Level). Another field is for how many we need to order. It is called (Order). I want to have Order's value automatically pulled by First adding (on hand) and (order level) together to know how many we have and are about to have. Now if that total level of the two is still less than the (opt stock level), then I want it to subtract the sum of the first to from (op stock level) and whatever is left over, enter that as the value for order.

I.E. (On Hand = 3, Order Level = 2, Optimum Stock Level = 9)...Order should equal 4. Because we need to have 9 on hand, but only have 3, however two are on the way, so we need to order 4 more.

**************
The Problem:
In Access, when I enter in the Criteria box in Design View, the following equation:
(([Optimum Stock Level]) - (([On Hand])+([Order Level]))) all that comes up in the View area is one record, and all zeros in there. There is plenty of data available, and there is fields set up so that it should work.
Can anyone explain to me why this won't work?
Also, that's not even includeing the part that we need to have it only when (on hand + order level) is less than (optimum stock level).
?????????
Thanks,
Jim
jimbob550@hotmail.com

 

[Dreamboat]

JimmyJoeJimBobEarl:

Do not (you can't) have calculations in a table. What you do is create a query that contains all of the fields from the table that you want, and then add a field that calculates that for you. Instead of looking at the table, you run the query to get that information.

MaryEllen (hee hee, not really)
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Ovatvvon]

I -have- been using a query for it. I tried putting the equation in the criteria section. But it doesn't pull up any information. If you want, I could send you a sample of the db so you can get a better understanding of what I'm talking about...it's usually easier once you see it, ya know?
Jim

p.s. Thank you for responding! =)

 

[Dreamboat]

Sure, send it!

But check this first:

=([Optimum Stock Level]) - (([On Hand])+([Order Level]))


is enough parentheses. Also, this equation assumes all field names come from same table or query. Otherwise, it'd be something like:

=(TableOneName![Optimum Stock Level]) - ((TableTwoName![On Hand])+(TableThreeName![Order Level]))

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Ovatvvon]

I sent a sample DB to your email:
techsupportgirl@home.com

Thank you for helping me!! =)

Jim

jimbob550@hotmail.com

 

[RickSpr]

Add a new query over the table you want to update (I used "Parts" as the table name). Change it to an Update query. Switch to SQL View and enter the following statement:

UPDATE Parts SET Parts.[Order] = [Optimum Stock Level]-([On Hand]+[Order Level])
WHERE ((([On Hand]+[Order Level])<[Optimum Stock Level]));

You can then switch back to Design View to see how you'd set it up in the design grid. Rick Sprague