DSum

[FranD]

I have the following expression in a Totals query. The error message I'm getting is "This expression is typed incorrectly or is too complicated to be evaluated...."

12MoChgs: DSum("[Charges]","[AR_Rec]","[Month]">=(DateAdd("m",-11,[Forms]![frmAR_RecDialog]![RptMonth]) And "[Month]"<=[Forms]![frmAR_RecDialog]![RptMonth]))

In plain english, I'm trying to sum all charges over a 12 month period - beginning with the current (selected) report month.

Any help would be greatly appreciated!!!

 

[dcx693]

Perhaps this syntax will work:
12MoChgs: DSum("[Charges]","[AR_Rec]","[Month]>=(DateAdd('m',-11,[Forms]![frmAR_RecDialog]![RptMonth]) And [Month]<=[Forms]![frmAR_RecDialog]![RptMonth])")

 

[DB7]

Maybe this...

12MoChgs: DSum("[Charges]","[AR_Rec]","[Month]>= #" & DateAdd("m",-11,[Forms]![frmAR_RecDialog]![RptMonth]) & "# And [Month]<= #" & [Forms]![frmAR_RecDialog]![RptMonth] & "#")

 

[WayneRyan]

Fran,

What are the [Forms]![frmAR_RecDialog]![RptMonth] values?

Are they just a numeric month?

This would be way easier with a date field!

This deals with months, not years.

12MoChgs: Nz(DSum("[Charges]", _
                  "[AR_Rec]", _
                  "[Month]  >= " & Month(DateAdd('m', -11, [Forms]![frmAR_RecDialog]![RptMonth])) & " And " & _
                  "[Month] <= " & Month([Forms]![frmAR_RecDialog]![RptMonth]), 0)

If the values are just a numeric month, then you have to do the modulus arithmetic
and also check the year. 4/04 is > 10/03. Need to know datatypes.

Wayne

 

[FranD]

Thank you!

Thank you.. thank you... thank you...!! It works!

Maybe this...

12MoChgs: DSum("[Charges]","[AR_Rec]","[Month]>= #" & DateAdd("m",-11,[Forms]![frmAR_RecDialog]![RptMonth]) & "# And [Month]<= #" & [Forms]![frmAR_RecDialog]![RptMonth] & "#")

 

[dcx693]

Thanks DB7. I hate D formulas!

 

[FranD]

One Final Question

Now can you tell me how to have the result be displayed as currency??
Thanks again!

Maybe this...

12MoChgs: DSum("[Charges]","[AR_Rec]","[Month]>= #" & DateAdd("m",-11,[Forms]![frmAR_RecDialog]![RptMonth]) & "# And [Month]<= #" & [Forms]![frmAR_RecDialog]![RptMonth] & "#")

 

[WayneRyan]

Fran,

12MoChgs: Format(DSum(...), "$###,###")

Wayne

 

[FranD]

Thanks Again

Viola!! Thanks again Wayne. Your help is much appreciated.
Fran

Fran,

12MoChgs: Format(DSum(...), "$###,###")

Wayne