Domain Aggregate Formula Help

[Erica Schoenefeld]

First, I'm taking a class on Access, so this stems
from my textbook:

I have a report where I am trying to subtotal
contract amounts by city in a report's group
footer. I've needed similar formulas in other
databases, which I've gotten to work correctly,
but it doesn't seem to in this case.

Old formula where I had to subtotal by BonusQuota
(worked):
=DSum("Salary","Recruiters-EGS","[BonusQuota] = "
& [BonusQuota])

New formula where I have to subtotal by City
(doesn't work):
=DSum("ContractAmt","ClientsAndTheirContracts-EGS","[City]
= " & [City])

I can't seem to figure out why the one works and
the other doesn't. The only difference I can
guess is that the Salary field comes from a table
whereas the ContractAmt field comes from a query
(that I created previously). Does it have
something to do with the fact that BonusQuota is a
number and City is a word? Maybe my syntax is
wrong for the type of answer I'm looking for?

Any help would be greatly appreciated!

Thanks!
Erica

 

[Guest]

Hi Erica,

If City is a word (text field), you will need to enclose it in single quotes
like this:

=DSum("ContractAmt","ClientsAndTheirContracts-EGS","[City] = '" & [City] &
"'")

that's [City] = single quote double quote & [City] & double quote single
quote double quote

Damian.

 

[Erica Schoenefeld]

THANK YOU so much, Damian! It works perfectly!

Erica

Hi Erica,

If City is a word (text field), you will need to enclose it in single quotes
like this:

=DSum("ContractAmt","ClientsAndTheirContracts-EGS","[City] = '" & [City] &
"'")

that's [City] = single quote double quote & [City] & double quote single
quote double quote

Damian.

:

First, I'm taking a class on Access, so this stems
from my textbook:

I have a report where I am trying to subtotal
contract amounts by city in a report's group
footer. I've needed similar formulas in other
databases, which I've gotten to work correctly,
but it doesn't seem to in this case.

Old formula where I had to subtotal by BonusQuota
(worked):
=DSum("Salary","Recruiters-EGS","[BonusQuota] = "
& [BonusQuota])

New formula where I have to subtotal by City
(doesn't work):
=DSum("ContractAmt","ClientsAndTheirContracts-EGS","[City]
= " & [City])

I can't seem to figure out why the one works and
the other doesn't. The only difference I can
guess is that the Salary field comes from a table
whereas the ContractAmt field comes from a query
(that I created previously). Does it have
something to do with the fact that BonusQuota is a
number and City is a word? Maybe my syntax is
wrong for the type of answer I'm looking for?

Any help would be greatly appreciated!

Thanks!
Erica