Getting Error in DSum Formula (1 Viewer)

[Mark1982]

here is My Formula:

Total: DSum("[Invoice Amount]","[PR33103]","[PR ID]<=" & [PR ID])

Whats Wrong?

 

[Ranman256]

Total: DSum("[Invoice Amount]","[PR33103]","[PR ID]<=" & me.PR_ID)

Is PR33103, a query (or table)?

The last param should be the form text box. Use ME.fieldName.
Using spaces in names can cause problems.

Typing the period after ME will show the field names. (Helps with spaced names)

And here, PRID is numeric. If it is string,you must use quotes.

 

[Mark1982]

Dear Ranman256:

PR33103 is the Table name.
let me tell you i am applying the above formula in query.

 

[Mark1982]

please see the attachment what i am getting error.

 

[arnelgp]

You can use Me in query.
use the Form format:

DSum("[Invoice Amount]","[PR33103]","[PR ID]<=" & [Forms]![yourForm]!PR_ID)

 

[isladogs]

1. You can't use 'Me.' in a query ...which is what I think Arnel meant to write
Me. is used in a form or report to indicate another control on the same form / report

The original expression should be used in a query
DSum("[Invoice Amount]","PR33103","[PR ID]<=" & [PR ID])
or refer to a specific form as in arnel's post

2. In the Totals row for the DSum field, change it from Sum to Expression

3. I'm not convinced the <= operator is correct.

 

[Ranman256]

You don't use DSUM in queries.
The query is the Dsum.

 

[Minty]

It would be much more efficient to use a sub query rather than a domain function.
The subquery will only run once, the DSum() function will be called individually for every record.

Have a read here http://allenbrowne.com/subquery-01.html

 

[CJ_London]

so - your formula as originally stated is correct - tho' you would not normally use domain functions (dsum, scount, etc) in queries - too slow and in other scenarios cannot deliver the levels of complexity

the reason it fails is because you have a left join to the table with the PR ID field - so if such a record does not exist, it will return null. Try either

DSum("[Invoice Amount]","[PR33103]","[PR ID]<=" & nz([PR ID],0))

Strongly recommend you remove spaces from table and field names - they will only cause you problems down the line

 

[Mark1982]

Dear CJ,

I apply your Formula but still i got problem.

Please see the attachment.

 

[Minty]

It's a grouped (Totals) query, remove the dsum completely and either simply put the field in, or change the grouping to Expression. (as per Ridders suggestion)

You should still create either a sub query or another totals query to then left join to this query.