How can I ensure calculated field will alway be positive

[ardagh]

I have a calculated field which sums the quantity field for all transactions by the part#, lot# and location. (A part number could be in several different locations and lots and are treated sperately - if they are in the same location with the same lot number they are summed).

To add quantity the user enters the data using a form. To issue (subtract) the user uses a minus number (eg -20). The calculated field sums all these and outputs the current total.

How can I make sure this is always positive.

Ie. When the user does an issue the qty they specify will be less than or equal to the current total according to the sum calculation.

Thank you for any help

 

[zoroaster]

Use the Int() function - not sure on the specifics of what you're asking but you should be able to work a way of using the Int() function in a way that displays only positive results:
Int(4) = 4
Int(-4) = 4

Please don't feel patronised by this suggestion, I know it's a bit basic, but without the specifics of what you're working with it's difficult to offer anything more specific.

Laters, Z

"42??? We're going to get lynched!

 

[mik18]

Use the absolute value function Abs().

mike

 

[Golom]

zoroaster

Actually no ... Int truncates the digits after the decimal place. For example

Int(3.8) = 3

To force a result to be positive, use the Abs function as mik18 suggests.

 

[zoroaster]

Oh darn, sorry, of course you're right, I feel so stupid now! Ardagh, so sorry for wasting your time, I should have given it more thought before I replied.

I hang my head in shame!

Laters, Z

"42??? We're going to get lynched!

 

[ardagh]

You are probably correct but I don't know anything about VB.

Ideally I would like to put the required code in the click event of the save button in the frmissuestock . This would check the value in the qty text box in frmissuestock against the value in the SumOfQty field in the qrySum (for that material/lot# and location) and return an error message if the quantity entered in the frmissuestock was greater than the quantity in the SumOfQty field in qrySum.