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

 

[Pat Hartman]

Date functions only work on date fields. If you have a field named Month, that sounds like it only contains a month value so you can't use DateAdd() on this field. Also, "Month" is the name of a function and this could easily be causing the problem. SQL and VBA become confused when you do anything with columns that are function or property names.

Many of us believe that Jet simply should not allow you to create columns named Month, Date, Section, or Name for example but Microsoft disagrees due to the large body of existing work that would fail so Jet just lets you get into trouble without even a warning.

 

[FranD]

Thanks Again

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

Fran,

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

Wayne