Using DSum with AND in expression

[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

 

[Van T. Dinh]

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))

 

[Damon Heron]

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

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)

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

 

[Van T. Dinh]

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.