Sum or Dsum or........VBA

[BLFOSTER]

How do I reference the current recordset on a CONTINUOUS form and "Sum" the wage field in this recordset based on criteria?



So on Form Current:
Mytextbox = Dsum("wage", CURRENT RECORDSET, "MyField = 'xxx'")

My intention is to compare average wage on employment of clients belonging to 2 separate agencies.

The recordset changes based on button pushed by user.

Need the average wage to change according to current recordset.

I have written several expressions to average wage, but since the recordset is on a continuous form, the average wage result is based on whichever agency appears at the top of the selected list. I have expressions to count instances of each agency in the recordset. I need to "sum" the wage field for each agency. I then have expressions to divide for average wage of each agency.

[June7]

Domain aggregates do not work on the form's recordset, they must reference a table or query object.

[BLFOSTER]

hmmm....other options? I have tried "sum" but don't know the syntax.

Thank you!

[June7]

Did you try Sum or Avg expressions in textboxes in the form header or footer section?

Did you say the form is filtered by agency?

The DSum (or DAvg, DCount, etc) would be like:

Dsum("wage", "tablename", "[Agency] = 'agency name'")

However, that will not consider other filtering on the form, such as a date.

[BLFOSTER]

Yes, I tried those. The problem is the changing recordset of the continuous form. The form is not filtered by agency so all functions/expressions I have tried are performed for the agency who lands at the top of the list. I need the expressions to pick out the records that belong to each agency and sum or average the wage in those records separately. Right now I am working on 2 subreports in the header of the continuous form whose recordsources change to the same as the form on Current, thinking I can isolate each agency that way and add a sum or average function to the subreports footer but no luck so far!
Getting "syntax error in "from" clause message - have no idea if this would ever work or not but DO hate to give up
EXAMPLE:
Forms!FORM2H!AvgR1.Report.RecordSource = "SELECT * FROM Forms!FORM2H!Form3H!.RecordSource " _
& "WHERE [Expr5]='xyz'"

[BLFOSTER]

I figured it out....just had to write expression on the form right! Simple fix, but sure beat my head against the wall about it. Thanks for your help!

[June7]

If the form is not filtering on agency, then form header/footer textbox:

=Sum(IIf([Agency]="xyz",[Wages],0))

Is that what you did?

[BLFOSTER]

yes, exactly! I don't know why I didn't try that order of events earlier...smh