Calculation problem

[DMT Dave]

Hi Guy's can anyone help with this one please



I have 3 item numbers in tblEdit with 200871 added with status planning

tblStock shows we have got 3 in stock

i am wanting to achieve looking up if any of this number is allocated ie: planning or on hold and recalculate in the stock table the following

StartQty 3
Allocated Qty 3
NewQty 0

The following is the code i have that is returning

StartQty 3
AllocatedQty 10769
NewQty -10766

There was only ever 3 in stock ?????

Dim mHold As String, mPlan As StringDim mLiftNo As Long, mStartQty As Long, mAllocated As Long, mNewQty As Long


mStartQty = Me.StartQty
mLiftNo = Me.LiftNo
mHold = "On Hold"
mPlan = "Planning"


If mStartQty > 0 Then
If IsNull(mAllocated = DCount("DelTo", "tblEdit", "[LiftNo] Like ""*" & mLiftNo & "*""" & " And [Status] = '" & mHold & "'" & " Or '" & mPlan & "'")) Then
mAllocated = "0"
mNewQty = "0"
Me.AllocatedQty = mAllocated
Me.NewQty = "0"
Else
mAllocated = DCount("DelTo", "tblEdit", "[LiftNo] Like ""*" & mLiftNo & "*""" & " And [Status] = '" & mHold & "'" & " Or '" & mPlan & "'")
mNewQty = mStartQty - mAllocated
Me.AllocatedQty = mAllocated
Me.NewQty = mNewQty
End If
End If

[Minty]

Create a normal query with some hard coded values to get your check your dcount results.

I would suspect your use of wildcards is messing with your results - this bit "[LiftNo] Like ""*" & mLiftNo & "*""" does not look right to me at all .
It should look like "[LiftNo] Like *" & mLiftNo & "* AND "

If your mLiftnumber is a zero length string or null it will return everything ,same with your other criteria.

[DMT Dave]

Thank you minty, it was excatly what you said, changed the way it was looking up and worked, thank you