Solved

Complicated DSum formula problem

Posted on 2013-01-31
2
332 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 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