DSum expression help

[murphy123]

I am trying to create a Dsum calculation using a text box on a form just to display to the user what the Current Balance Owed of an employee is. I want to sum the total amount of checks that are showing on the subform received by an employee and deduct this sum of all checks from the employee's Starting Balance. However, I am sure I am missing something. When I enter the following on my text box control source:
=DSum("([Start Balance]-[CHECK_AMOUNT])","Payment_History","[Employee_ID]") my end results is SUM of everything including the Start Balance. The subtraction expression is not working. Can anyone help me and let me know what I am missing? Thanks.

 

[dhookom]

How many starting balances are there? Does each record in Payment_History have its own Start Balance? Do you want to filter by Employee? You current "[Employee_ID]" in the criteria does nothing to filter out anyone.


Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya murphy123 . . .

My read is that your criteria is lacking ... your not pinging properly! You show:

[blue]=DSum("([Start Balance]-[CHECK_AMOUNT])","Payment_History",[red][b]"[Employee_ID]"[/b][/red])[/blue]

[blue][purple]criteria[/purple]: An optional string expression used to restrict the range of data on which the DSum function is performed. For example, [purple]criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE.[/purple] If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.[/blue]

Considering the above and that [blue]your intent is to ping on the employee in the form[/blue], your criteria should be something like:

[blue]"[Employee_ID] = " & Forms![purple][B][I]MainFormName[/I][/B][/purple]!Employee_ID [green]'if Employee_ID is numeric[/green]

or

"[Employee_ID] = '" & Forms![purple][B][I]MainFormName[/I][/B][/purple]!Employee_ID & "'" [green]'if Employee_ID is text[/green][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]