Results 1 to 11 of 11

Thread: Negative Value not allowed

  1. #1
    Join Date
    Aug 2009
    Posts
    50

    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

  2. #2
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Look up Help for the ABS function.
    Allan

  3. #3
    Join Date
    Jan 2009
    Location
    Portland, Oregon
    Posts
    21
    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?
    Bob Larson
    Former Access MVP
    2008-2010

  4. #4
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by boblarson View Post
    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?

  5. #5
    Join Date
    Jan 2009
    Location
    Portland, Oregon
    Posts
    21
    You could do something like this:

    IIf([Field1]<=[Field2],[Field2]-[Field1],0)
    Bob Larson
    Former Access MVP
    2008-2010

  6. #6
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by boblarson View Post
    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

  7. #7
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Bob gave you a generic example, now for your original question
    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.
    Allan

  8. #8
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by Allan Murphy View Post
    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

  9. #9
    Join Date
    Jan 2009
    Location
    Portland, Oregon
    Posts
    21
    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
    Bob Larson
    Former Access MVP
    2008-2010

  10. #10
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by boblarson View Post
    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

  11. #11
    Join Date
    Jan 2009
    Location
    Portland, Oregon
    Posts
    21
    Quote Originally Posted by FMKA View Post
    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.
    Bob Larson
    Former Access MVP
    2008-2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •