Using DSum with AND in expression

  • Thread starter Thread starter Damon Heron
  • Start date Start date
D

Damon Heron

I have the following code to check inventory of product after quantity is
input in subform:

Dim OnHand As Long
Dim PID As Integer
Dim LID As Integer
PID = Me![ProductID]
LID = Me![LocationID]
OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", "[Inventory
Transactions]", "[ProductID]=" & PID)

This works, gives me the total quantity of the product but in all
Locations.... I tried adding the following to the criteria:

AND [LocationID]='LID' ")
in hopes of getting just the quantity at the particular location,
but am getting a type mismatch error. Tried various quotes, apostrophes,
can't get it to work... Is it possible? The examples in Access help don't
address my circumstance too well.

Thanks in advance for any suggestions!!

Damon
 
Assuming the Field LocationID is a Numeric Field in your Table [Inventory
Transactions], try:

OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", _
"[Inventory Transactions]", "[ProductID]= " & PID & _
" AND [LocationID] = " & LID)

Actually, you don't have to create variables PID and LID. Try:

OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", _
"[Inventory Transactions]", "[ProductID]= " & Nz(Me![ProductID], 0) & _
" AND [LocationID] = " & Nz(Me![LocationID], 0))
 
Thanks for the help - I used the second method, and it works. I am curious
though, about the (Me![ProductID],0) part. What does the zero refer to -
the column?

Damon

Van T. Dinh said:
Assuming the Field LocationID is a Numeric Field in your Table [Inventory
Transactions], try:

OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", _
"[Inventory Transactions]", "[ProductID]= " & PID & _
" AND [LocationID] = " & LID)

Actually, you don't have to create variables PID and LID. Try:

OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", _
"[Inventory Transactions]", "[ProductID]= " & Nz(Me![ProductID], 0) & _
" AND [LocationID] = " & Nz(Me![LocationID], 0))

--
HTH
Van T. Dinh
MVP (Access)



Damon Heron said:
I have the following code to check inventory of product after quantity is
input in subform:

Dim OnHand As Long
Dim PID As Integer
Dim LID As Integer
PID = Me![ProductID]
LID = Me![LocationID]
OnHand = DSum("(nz([UnitsReceived])-nz([UnitsRemoved]))", "[Inventory
Transactions]", "[ProductID]=" & PID)

This works, gives me the total quantity of the product but in all
Locations.... I tried adding the following to the criteria:

AND [LocationID]='LID' ")
in hopes of getting just the quantity at the particular location,
but am getting a type mismatch error. Tried various quotes, apostrophes,
can't get it to work... Is it possible? The examples in Access help don't
address my circumstance too well.

Thanks in advance for any suggestions!!

Damon
Click to expand...
Click to expand...
 
It does not refer to Column. You should read it as the function Nz():

Nz(Me![ProductID], 0)

Basically, it means if Me![ProductID] is Null, replace it with zero.

You used Nz() in the same expression but you didn't use the second argument.
Check Access VB Help on the Nz() function.
 
Back
Top