[AccessD] Reorder Level

I quick check in Excel shows that your calculation results in...

On Hand	496
On Order	500
Reorder Amt	982

But (On Order - On Hand) + (Reorder Level) gives

On Hand	496
On Order	500
Reorder Amt	18

And wrapping that in an Iif statement [ Iif((On Order - On Hand) + (Reorder Level)>0,(On Order - On Hand) + (Reorder Level),0) ] prevents negative reorder amounts.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Tuesday, November 10, 2009 10:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Reorder Level

I think you want (On Hand + On Order) - (Reorder Level) .

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Tuesday, November 10, 2009 6:37 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Reorder Level

I want to show if an item is below the reorder level. The field is ReorderLevel. They enter the amount they need to keep in stock.  The problem is what if they are over stocked. For Example:  Reorder Level:
14. On Hand: 496 & On Order: 500. txtReorderLevel shows "Item is -982 units below minimum level.

 

I created a text box DaysPastDue that shows if they are below the reorder level. 

The control source of DaysPastDue ="Item  is " & [txtReorderAmt] & "
units " & "below minimum level".

 

The control source of txtReorderAmt:
=[ReorderLevel]-([txtOnHand]+[txtOnOrder])

 

OnCurrent:

If Not IsNull(Me!ReorderLevel.Value) Then 'There is a reorder level entered

       ReorderLevel = Me!ReorderLevel.Value 'Show the value of the reorder level entered

         

    If txtReorderAmt > 0 Then 'Reorder level is below.      

 Me!ReorderLevel.ForeColor = lngRed 'reorder level is red

              Me.DaysPastDue.Visible = True 'Show past due label

    Else

        Me!ReorderLevel.ForeColor = lngBlack 'Reorder level is not low, reorder level is black

               Me.DaysPastDue.Visible = False 'No order label.

        

    End If

 End If

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com