access formulas

[peterengland]

As part of this database I am creating there is a stock control side to it. The table that refers to the stock has the following fields: Stock code, stock description, stock level, Reorder level, Stock Status. There is also a stock form that allows the user to enter the stock details.

In the past when creating a stock system in excel I have created a formula that looks at the Stock level cell compares it with the reorder level cell and in the stock status cell returns ok if stock level is higher that reorder level or reorder if the opposite applies.

What I want to do is the same in access so on the stock form there is a field that states either ok or reorder which reacts to the information entered in stock level. I can then do a query to determine which items need reordering.

I am completely new to access so have no idea how to do this or even if this is possible. Any help would be much appreciated. I am currently using Access 2000. Kinds Regards Peter

 

[Andrew Fergus]

Hello Peter and welcome to Mr Excel.

This is definitely possible. However, when I read your post, I couldn't work out if you are entering the stock level into a proper form or directly into the table (you mentioned "cell" which is typical of entering data into a table).

If you are using a form then this is how you could update that field automatically :

Go into the form design, right click the 'stock_level' field (or whatever you have called it) -> click Properties -> Event Tab -> After Update -> Click the ... button to activate the Builder -> Code Builder -> Ok -> enter this (without the two outermost quotes) "[Status] = Iif([stock level] <= [reorder], "Reorder", "Ok") -> Save and close the VB window -> Save the form.

The code should look like this (before you close the VB window):

Private Sub stock_level_AfterUpdate()
  [Status] = Iif([stock_level] <= [reorder], "Reorder", "Ok")
End Sub

Be sure to use your actual field names from your form. Any time you change the stock level on the form, then the status will update automatically. You can't use this method when entering data directly into the table.

If you are entering the data directly into the cell (not the preferred method for entering data, but what the hey?) then another approach might be to do this in your query instead. You mentioned that you would run a query to identify the items that need to be reordered, rather than testing for items where the Status = Reorder, you could test for items where the stock_level is less than or equals the reorder level. How? In the query design screen, in the criteria section under the 'stock_level' field, enter this (without the outermost quotes) : "<=[Reorder]"

Again, make sure you use your actual field names. This will return all items that need to be reordered without using the status field.

If you get stuck, post a reply in this thread.

HTH, Andrew

 

[peterengland]

Thank you Andrew it worked a treat. I used the first suggestion as I was working with forms and it was exactly what I wanted it to do. Thanks again
Peter

 

[siamsunset]

Thanks heaps for you help.

Just 2 questions, i can do these calculations using multiple tables? for example, a price from table A + a price from table B and have the result record in table C?

Also i followed the directions in the link provided and it works ok but to resolve the equation i have to enter a number in the total box and hit enter for it to display the answer to my equation. Is there a way that is i enter in the value in txtbox1 and txtbox2 and the answer autofills in txtbox3?

 

[SydneyGeek]

Yes you can do the calcs using multiple tables but you need to pull everything together in a single query first. Build a query based on 2 tables (3 if required) and view it. If the asterisk in the record selector is black, you can use it for data entry. If not, you may have to rethink your query design. Always make sure that you include the Primary Key for the "main" table -- the one the from is based on -- and the joining keys that link it to the other tables. Otherwise you'll have a dimmed asterisk and no data entry options.

Denis

 

[Andrew Fergus]

Hi Peter
I realised after the event that we should include some basic error checking in the code (e.g. test for null values) so I recommend you change this :

[Status] = Iif([stock_level] <= [reorder], "Reorder", "Ok")

to this:

[Status] = Iif(Nz([stock_level]) <= Nz([reorder]), "Reorder", "Ok")

What do you want to show both values =0? One way around that is to change the <= to <.
Andrew

 

[peterengland]

Thanks again Andrew for all your help. Kinds Regards Peter