Calculation problem

[DMT Dave]

Hi Guy's, can someone tell me where i am going wrong here please, i am adding ie: 15 new stock items and the 6 digit number am wanting to lookup or count if i have added these into another table, if i have, the status would be planning or oh hold



so we may have added 3 of these but still waiting stock so the answer i am looking for is StartQty = 15, Allocated = 3 NewQty = 12

I MEANT TO ADD THAT THE RESULT IS ADDING as allocated 10709 and NewQty -10694 ! Bizarre

Also if we have added 3 of these in tblEdit, they are always added with this 6 digit number first then maybe followed with hypen and client name

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


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


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

[rpeare]

you have posted this same question in various forms 3 or 4 times now I think, how about posting an example of your database.

The only thing I see leaping out at me is your OR statement is incorrect

it should be something like:

mAllocated = dcount("LiftNo", "tblEdit", "[LiftNo] like ""*" & mliftno & "*"" and ([Status] = '" & mhold & "' OR [Plan] = '" & mplan & "')")

you'll have to play around with your bracketing as well depending on what you are actually trying to do because your bracketing will determine everything

what you have now

X AND Y OR Z

may get evaluated in ways you don't expect

If you mean to say X must be true AND either Y must be true or Z must be true your bracketing should be

X AND (Y OR Z)

[pbaldy]

Unless I'm goofed up, and I've got a glass or 2 of wine in me, this test will never be met:

If IsNull(mAllocated = DCount(...)) Then

The "=" test in there will result in a true/false result, not a Null. Plus you haven't set mAllocated at that point, so it will always be 0.