Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Complicated DSum formula problem

Posted on 2013-01-31
Medium Priority
343 Views
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
Question by:SteveL13
• 2

LVL 59

Expert Comment

ID: 38841495
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

LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38841507
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month11 days, 6 hours left to enroll