• Status: Solved
• Priority: Medium
• Security: Public
• Views: 345

# Complicated DSum formula problem

I seem to have a syntax issue with:

'Display message box to warn user that excess inventory exists for this part number and allow for applied inventory qty ...
If DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") > 0 Then
txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & (DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") & " - " & (DSum("[AppliedInvQty]", "tblOpenOrders", "[Part_No] = '" & Me.txtPart_No & "'" And [Complete] = False) & " .  Enter the qty you want to apply to this order: (Be careful to enter the exact amount)")))

Can someone help me with this?
0
SteveL13
• 2
1 Solution

PresidentCommented:
Dim lngTotal as long

lngTotal = Nz(DSum("[Qty]", "tblFinGoods", "[PartN] = " & chr\$(34) & Me.txtPart_No & chr\$(34)) ,0)

If lngTotal > 0 then
txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & lngTotal & " - " & DSum("[AppliedInvQty]", "tblOpenOrders", "[Part_No] = " & chr\$(34) & Me.txtPart_No & chr\$(34) & " And [Complete] = False) & ".  Enter the qty you want to apply to this order: (Be careful to enter the exact amount)")
End If

Give that a go...

Jim.
0

PresidentCommented:
and if that doesn't work, break it down more like I did with lngTotal.  Ie.

Dim lngTotal as long
Dim lngTotal2 as long

lngTotal = Nz(DSum("[Qty]", "tblFinGoods", "[PartN] = " & chr\$(34) & Me.txtPart_No & chr\$(34)) ,0)

If lngTotal > 0 then
lngTotal2 = DSum("[AppliedInvQty]", "tblOpenOrders", "[Part_No] = " & chr\$(34) & Me.txtPart_No & chr\$(34) & " And [Complete] = False")

txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & lngTotal & " - " & lngTotal2 & ".  Enter the qty you want to apply to this order: (Be careful to enter the exact amount)")
End If

until you get it.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.