dsum calculated criteria

[dark11984]

Hi,
Just want to get some clarification on whether or not it is possible to have a calculated criteria in the DSum function.

I have the following expression as my control source in a textbox. Textbox returns nothing. If it is possible, can anyone see where i am going wrong?

=DSum("[totalspend]","[Qryspend]","[FisYear]=Year(DateAdd('m',6,Date()))")

Cheers,

Nick

 

[vbaInet]

Is FisYear actually a Year or is it just a date (formatted as a year perhaps)?

Try this instead:

=DSum("[totalspend]","[Qryspend]","[FisYear]=" & Year(DateAdd("m",6,Date())))

 

[dark11984]

FisYear is a date formatted as a year. Yep that worked however ive realised that ive got to have two criterias similar to below.

DSum("[totalspend]","[Qryspend]","[FisYear]=" & Year(DateAdd("m",6,Date())) And "[qryspend].[cmsref]=" & [tblcontract].[cmsref])

Thanks

 

[vbaInet]

If the formatting is done in the query then you need Year([FisYear]) if it's done in the table then it's fine.

Concatenate the And properly. It's not a calculated value so it needs to be inside the quote. Also, reference the control of cmsref.

 

[dark11984]

The formatting is done in a different query i.e. QrySpend

What do you mean reference the control of cmsref?

I've tweaked the code a little bit...

FYTDSpend: DSum("[totalspend]","QrySpend","QrySpend.[cmsref] = '" & [tblcontract].[cmsref] & "'" & " And year(QrySpend.[FisYear]) = #" & Year(DateAdd("m",6,Date())) & "#")

... and now i'm getting error in the text box and a syntax error.

Syntax error in date in query expression 'QrySpend.[cmsref] = 'CON-10-3080'And QrySpend.[FisYear] = #2011#'.