Calculate a number field?

[Zoon]

I use a 6 year old program that uses Access 97 databases. I also have Access 97 and a beginners knowledge of how to use it. I want to add a number field (I know how) and use the number entered in this new field to calculate the number in another field (I do not know how).

The fields are; Code (the key, text), Order (number), and the new field will be Quantity (number).

The Quantity field will contain from zero up to 36. If the number entered in Order is 12 and the number in Quantity is zero then Order will stay zero. But if the number entered in Order is 12 and the number in Quantity is 12 then Order will become one. If the number in Order is 12 and the number in Quantity is 6 then Order will become 2. The formula would be; Order divided by Quantity.

I would appreciate any help.

 

[PaulBricker]

Zoon, you have two fields, Order and Quantity. If the number in Order is 12, are you saying you want to divide that number by Quantity and CHANGE the value of Order to 1? That's my only confusion here. The rest is easy. In the after update event for Quantity you put
Me.Order = IIf(Me.Quantity = 0,0,Me.Order/Me.Quantity)

Paul

 

[Zoon]

Thanks for responding Paul.

I need 2 fields, Order is an existing number field, and I want to add a new number field named Quantity. I want to divide the number in Order by the number in Quantity. The number in Order will change.

I want to add the new field because the program makes errors on the Purchase Order Quantity unless we manually change the number in the Order field. This manually recalculating is also causing errors.

I do not know what a "after update event" is. Do you mean a SQL Update query? I do know a little about VB, so I understand IFF().

Can I put your IFF formula in an Access 97 field like I can do in Excell?

 

[PaulBricker]

OK, this is what you will need. I assume that you have added your field Quantity to the Table(you said you could do this). Then I assume you are doing this in a Form. If you are, then to get to the After Update event for Quantity you do this
1. Open the form in design view
2. Open the Properties box. On the menu bar it's View..Properties
3. Click on the Quantity control on the Form and then in the properties box scroll down till you see the After Update event.
4. Click on the line next to the After Update event. You will see a little button with three dots ... Click on that button, Click on Code Builder, click OK.
5. This will bring up the After Update event for the Quantity control.
6. Place the IIf statement in that event. It should look like this
Private Sub Quantity_AfterUpdate()
Me.Order = IIf(Me.Quantity = 0,0,Me.Order/Me.Quantity)
End Sub

7. Close the module window and make sure that the words (Event Procedure) are showing on the line next to After Update.
8. Close it out and save the changes.

The only thing you willl need to do is make sure your Controls are named Order and Quantity. If they aren't, you will have to change the names in the IIF statement to match yours. Post back with any problems.

Paul

 

[Zoon]

Thanks for the help. This inventory control program has a bunch of forms but none are listed when using Access 97 to open the tables. I will work on this using your direction.