Negative Value not allowed

Gents,

I do not whant negative value in my calculation for instance

5-12= -7 what kind of VBA code can i use for not allowing negative value note that i want to use for inventory control

Look up Help for the ABS function.

Do you just do not want negatives, or do you want it so you can't subtract more than is available?

So, using the Abs function as Allan has said, would yield 7 instead of -7 but if you want only to subtract what is available (so in this case you would only want to subtract 5 instead of 12 because 5 is all that is left) that would require some other calculation. So, which would it be?

Quote:

---

Originally Posted by boblarson

Do you just do not want negatives, or do you want it so you can't subtract more than is available?

So, using the Abs function as Allan has said, would yield 7 instead of -7 but if you want only to subtract what is available (so in this case you would only want to subtract 5 instead of 12 because 5 is all that is left) that would require some other calculation. So, which would it be?

---

Good Morning Bob

your word underlined here bellow that is exactly i need provide me the calculation

(so in this case you would only want to subtract 5 instead of 12 because 5 is all that is left) that would require some other calculation. So, which would it be?

You could do something like this:

IIf([Field1]<=[Field2],[Field2]-[Field1],0)

Quote:

---

Originally Posted by boblarson

You could do something like this:

IIf([Field1]<=[Field2],[Field2]-[Field1],0)

---

Thanks a lot Bob but tell me one thing Bob! where am i going to put this expression ? in critera ? or in VBA. or in validation rule in form ? if you get time would you please send me a sample i think it will be more helpfull

I Apreciate your help

Bob gave you a generic example, now for your original question

Quote:

---

I do not want negative value in my calculation for instance

5-12= -7 what kind of VBA code can i use for not allowing negative value note that i want to use for inventory control

---

Please provide more details where you are using this calculation, is it on a form or in a report? The more details provided in your question, the easier it is for a specific response.

Quote:

---

Originally Posted by Allan Murphy

Bob gave you a generic example, now for your original question

Please provide more details where you are using this calculation, is it on a form or in a report? The more details provided in your question, the easier it is for a specific response.

---

Gents,

thanks a lot it worked according to Bob Illustration but what i need that the system cannot accept the entry this must give an error message saying that << Stock not available>> but here is accepting the entry and arround the value to zero

In the Before Update event (on the form) you can use

Code:

---

If Me.Field1< Me.Field2 Then
  Cancel = True
  If MsgBox("You can't enter a number greater than available stock.  Would you like to try again?", vbQuestion + vbYesNo, "Error") = vbNo Then
      Me.Undo
  End If
End If

---

Quote:

---

Originally Posted by boblarson

In the Before Update event (on the form) you can use

Code:

---

If Me.Field1< Me.Field2 Then
  Cancel = True
  If MsgBox("You can't enter a number greater than available stock.  Would you like to try again?", vbQuestion + vbYesNo, "Error") = vbNo Then
      Me.Undo
  End If
End If

---

---

Thanks Bob Now is perfect i am learning too much from you keep it going your carreer and God bless you Thanks

Quote:

---

Originally Posted by FMKA

Thanks Bob Now is perfect i am learning too much from you keep it going your carreer and God bless you Thanks

---

No problem and glad I could assist. :)