• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

DSum Criteria

I have the following code in a control source for a form field.

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "'")

But I need to change it to include criteria like AND [TRNX_CODE} = "I" or "O"

I tried this but it doesn't work:

= DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & Forms![frmParts]![txtPART_NO] & "'" AND [TRNX_TYPE] = "'" I "'" OR "'"O"'"

What am I doing wrong?
0
SteveL13
Asked:
SteveL13
  • 4
  • 3
  • 2
1 Solution
 
Dale FyeCommented:
Well, which is it [TRNX_CODE] or [TRNX_TYPE], it cannot be both.  Assuming it is [TRNX_TYPE], try:

= DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & Forms![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = IN('I','O')")
0
 
SteveL13Author Commented:
It is TRNX_TYPE.    Sorry I mistyped.

I tried:

= DSum("[QTY_ORDERED]", "tblInventory", "[PART_NO] = '" & Forms![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] = IN('I','O')")

but get an #Error in the field.

If it matters TRNX_TYPE is a text field.
0
 
HainKurtSr. System AnalystCommented:
try:

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_CODE] in ('I','O')")

or

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND (([TRNX_CODE]='I') OR [TRNX_CODE]='I'))")
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SteveL13Author Commented:
This worked:

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_CODE] in ('I','O')")
0
 
HainKurtSr. System AnalystCommented:
i guess both should work...

except copy & paste issue ^^^ :)

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND (([TRNX_CODE]='I') OR [TRNX_CODE]='O'))")
0
 
Dale FyeCommented:
before making this the control source of a control on your form, lets use the immediate window to do some testing.

Try the following in the immediate window (replace XXXXXX with a valid part number:
strCriteria = "[PART_NO] = 'XXXXXX'"
?strCriteria
?DSUM("[QTY_Ordered]", "tblInventory", strCriteria)

After typing or copying each of these lines, press the enter key.  Did everything work?
If not, then you have a problem with your [Part_NO] field.  If so, then try:

strCriteria = "[PART_NO] = 'XXXXXX' AND [TRNX_TYPE] = 'I'"
?strCriteria
?DSUM("[QTY_Ordered]", "tblInventory", strCriteria)

If that worked,then try
strCriteria = "[PART_NO] = 'XXXXXX' AND [TRNX_TYPE] = 'O'"
?strCriteria
?DSUM("[QTY_Ordered]", "tblInventory", strCriteria)

and finally:
strCriteria = "[PART_NO] = 'XXXXXX' AND [TRNX_TYPE] = IN ('I','O')"
?strCriteria
?DSUM("[QTY_Ordered]", "tblInventory", strCriteria)
0
 
Dale FyeCommented:
So, it wasn't [TRNX_TYPE] after all?

if, "this worked" then why did you give all the points to the other expert?
0
 
SteveL13Author Commented:
I typed wrong again.    It IS TYPE
0
 
SteveL13Author Commented:
This was the final:

=DSum("[QTY_ORDERED]","tblInventory","[PART_NO] = '" & [Forms]![frmParts]![txtPART_NO] & "' AND [TRNX_TYPE] in ('I','O')")
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now