DSUM Function Using Form Fields

Question

I have a form which is used to submit entries to a Table. The ‘Monthly Adjustment $’ Field currently posts new table entries which works fine.

I would like to display the total balance from the Raw Data Table in the ‘Current Balance $’ field filtered based on the Company and Accrual ID that is selected by the user in the Form.

Each time the user selects a new Company and Accrual ID combination in the Form I want the ‘Current Balance $’ field to display the total $ for that Company and Accrual combination from the Raw Data Table.

To start with I have tried using a DSUM in expression builder to sum the ‘Amount $’ field in the Raw Data table, where the Accrual ID is equal to whatever value the user has selected in the Form’s ‘Accrual ID’ field. The next step would be to then add a second filter for the company selected (which I will do once I figure this first bit out). I have included the expression I am using below.

=Nz(DSum("[Amount $]","Accruals Raw Data","[Accruals Raw Data]![Accrual ID]= ' " & [Forms]![Single Accrual Entry - Form]![Accrual ID] & " ' "))

However when I select an option in the Accrual ID field in the form it does not return any results in the Current Balance $ field. It just displays a blank box.

I can get the ‘Current Balance $’ to display the total $ amount from the table using DSUM (i.e. with no filters applied) but can’t get it to do the above.

Would appreciate your help.

Many Thanks

Answer 1

Remove spaces if ID is text:

=Nz(DSum("[Amount $]","Accruals Raw Data","[Accrual ID]= '" & [Accrual ID] & "'"),0)

and remove quotes too if ID is numeric:

=Nz(DSum("[Amount $]","Accruals Raw Data","[Accrual ID]= " & [Accrual ID] & ""),0)

For two criteria try:

=Nz(DSum("[Amount $]","Accruals Raw Data","[Accrual ID]= '" & [Accrual ID] & "' And [Company] = '" & [Company] & "'"),0)