Using Dsum to grab & total selected records in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

So far I have the following expression on a continuous form that is returning
zilch.

=DSum("[TertiaryShipQty]","[TblAssySubShipments]","[TblAssySubShipments]![AssyShipmentID]"="Me!TxtAssyShipmentID")

What I want dsum to do is return a total for the field TertiaryShipQty from
the table TblAssySubShipments but only for records where the field
AssyShipmentID from the same table equals the value in the current form's
text box called TxtAssyShipmentID.

I know it is a problem with the criteria because when this is omitted, dsum
dutifully returns the total of the whole table.

Thanks in advance.
 
So far I have the following expression on a continuous form that is returning
zilch.

=DSum("[TertiaryShipQty]","[TblAssySubShipments]","[TblAssySubShipments]![AssyShipmentID]"="Me!TxtAssyShipmentID")
Click to expand...

I presume because there are no records for which the AssyShipmentID
contains the text string "Me!TxtAssyShipmentID", which is what you're
searching for.

Try:

=DSum("[TertiaryShipQty]", "[TblAssySubShipments]",
"[AssyShipmentID] = " & TxtAssyShipmentID)

It's not necessary to qualify the table name in the criteria string
(Access already knows you're referencing the table), nor the Me! in
the value to be searched.

John W. Vinson[MVP]
 
Worked perfect; thanks.
I guess I just don't grasp the syntax with the & symbol at this point.

John Vinson said:
So far I have the following expression on a continuous form that is returning
zilch.

=DSum("[TertiaryShipQty]","[TblAssySubShipments]","[TblAssySubShipments]![AssyShipmentID]"="Me!TxtAssyShipmentID")
Click to expand...

I presume because there are no records for which the AssyShipmentID
contains the text string "Me!TxtAssyShipmentID", which is what you're
searching for.

Try:

=DSum("[TertiaryShipQty]", "[TblAssySubShipments]",
"[AssyShipmentID] = " & TxtAssyShipmentID)

It's not necessary to qualify the table name in the criteria string
(Access already knows you're referencing the table), nor the Me! in
the value to be searched.

John W. Vinson[MVP]
Click to expand...
 
Worked perfect; thanks.
I guess I just don't grasp the syntax with the & symbol at this point.
Click to expand...

& is simply a concatenation operator. The third argument to DSum()
should be a valid SQL WHERE clause (without the word WHERE). For
example, if the current record's AssyShipmentID is 3184, you want the
third argument to become

AssyShipmentID = 3184

To get this, you concatenate two strings: the boilerplate string
constant

"[AssyShipmentID]="

and a reference to the value of the form control

TxtAssyShipmentID

which (in this example) happens to contain 3184.

John W. Vinson[MVP]
 
Back
Top