Using Dsum to grab & total selected records in a table

[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.

 

[John Vinson]

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

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

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]

 

[Guest]

Worked perfect; thanks.
I guess I just don't grasp the syntax with the & symbol at this point.

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

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

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]

 

[John Vinson]

Worked perfect; thanks.
I guess I just don't grasp the syntax with the & symbol at this point.

& 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]