DSUM with multiple criteria and a syntax error

[Guest]

Hello All,

I have a report that is formatted to look like a crosstab that includes rows
even if there is no data for those rows. Suffice to say, I am using the
expression listed below to populate the report's fields. When I run the
expression with static values it works fine. However, now that I have added
the datepart() function I get the following error: "The expression you
entered contains invalid syntax." I know that it would have been better to
use a date data type field for the date, but I have no control over how the
data is sent to us.

Thankyou for anyhelp.

Sincerely,
Shaun

Dynamic Datepart that doesn't work
DSum("ShareTotal","tblXHolding","[tblXHolding].[ExpYr] =
'Cstr(DatePart("yyyy",Date()))' AND [tblXHolding].[ExpMth] = 11 AND
[tblXHolding].[AssetNm] = 'Municipal' And [tblXHolding].[IsuNm]= 'DDT: T -
REG'")

Static Value that works:
DSum("ShareTotal","tblXHolding","[tblXHolding].[ExpYr] = '2007' AND
[tblXHolding].[ExpMth] = 11 AND [tblXHolding].[AssetNm] = 'Municipal' And
[tblXHolding].[IsuNm]= 'DDT: T - REG'")

 

[Guest]

Try remove my crlfs with this expression:
DSum("ShareTotal",
"tblXHolding",
"[ExpYr] = Cstr(Year(Date())) AND [ExpMth] = 11 AND
[AssetNm] = 'Municipal' And [IsuNm]= 'DDT: T - REG'")

 

[Guest]

Duane,

Thank you very much. It worked like a charm.

Shaun

Try remove my crlfs with this expression:
DSum("ShareTotal",
"tblXHolding",
"[ExpYr] = Cstr(Year(Date())) AND [ExpMth] = 11 AND
[AssetNm] = 'Municipal' And [IsuNm]= 'DDT: T - REG'")

--
Duane Hookom
Microsoft Access MVP

Hello All,

I have a report that is formatted to look like a crosstab that includes rows
even if there is no data for those rows. Suffice to say, I am using the
expression listed below to populate the report's fields. When I run the
expression with static values it works fine. However, now that I have added
the datepart() function I get the following error: "The expression you
entered contains invalid syntax." I know that it would have been better to
use a date data type field for the date, but I have no control over how the
data is sent to us.

Thankyou for anyhelp.

Sincerely,
Shaun

Dynamic Datepart that doesn't work
DSum("ShareTotal","tblXHolding","[tblXHolding].[ExpYr] =
'Cstr(DatePart("yyyy",Date()))' AND [tblXHolding].[ExpMth] = 11 AND
[tblXHolding].[AssetNm] = 'Municipal' And [tblXHolding].[IsuNm]= 'DDT: T -
REG'")

Static Value that works:
DSum("ShareTotal","tblXHolding","[tblXHolding].[ExpYr] = '2007' AND
[tblXHolding].[ExpMth] = 11 AND [tblXHolding].[AssetNm] = 'Municipal' And
[tblXHolding].[IsuNm]= 'DDT: T - REG'")