Aggregate VBA Function with Group By query domain - not allowed

[ironfelix717]

Hi,

This is a persistent issue that i'm tired of working around, so i figured i'd finally make an attempt to solve.

The aggregate VBA function (Dsum) apparently doesn't work when the domain is a group-by query and the field to be calculated (summed) is a calculated expression??

The error tells me nothing useful (surprise). Documentation also mentions nothing (also shocking).

Proof is in the download.

Any ideas, be sure to reach out.

Thanks




Database1.zip

[ironfelix717]

Edit:

MS did not save my changes when creating the field, thus the function is not finding it, which is why its throwing an error. The query designer is annoying as usual.

Solved:

Query was defined by:

db.QueryDefs("Budget_ByCategory").SQL = "SELECT Budget.Category, Sum([MonthlyAmount]/30*" & DaysBetween & ") AS [Period_Expected] FROM Budget GROUP BY Budget.Category;"

Error line was:

var = DSum("Period_Expected", "Budget_ByCategory")

Error fixed is:

var = DSum("[Period_Expected]", "Budget_ByCategory")

Apparently brackets are optional for a field in almost every other part of Access except in functions like dsum - how cool

[Micron]

I have experienced this before; never knew what caused it even though I knew enough not to have names that required brackets. Thanks for that.
I suppose brackets are required in a domain function when the name is an alias. In your case, it is not a field and it is not a control. In my case, I cannot recall because it was too long ago.