Solved

Complicated DSum formula problem

Posted on 2013-01-31
2
331 Views
Last Modified: 2013-02-01
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
Comment
Question by:SteveL13
  • 2
2 Comments
 
LVL 57
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dlookup MSACCESS 5 25
VBA SQL statement - 2 "OR"s and 1 "And" 4 27
backup programme - VBA 3 24
Microsoft Access Delete all Records from table but Max 2 11
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question