How do I use Dsum? 1

[logopolis]

I understand the principal in using the dsum function, just not how to apply it.

I have a form (address). I wish to put a field, in currencvy format, in this form from a query from a subform (finance). Each record on the form has its own ID (AddressID) which is also used on the query. There are multiple records on the query against each ID. I would like to sum these records for one field in the query (payment). Can anyone tell me the formula to use with dsum on the form.

Many thanks

John

 

[PHV]

Something like this ?
=Nz(DSum("[Name of payment field]", "[Name of payment table/query]", "[Name of AddressID field]=" & Me![Name of AddressID control]), 0)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

I have entered the formula as follows

=Nz(DSum("[SumOfTotal]","[TotalCost5]","[AddressID]=" & [Me]![AddressID]),0)

Where SumOfTotal is the field in the query
TotalCost5 is the name of the Query
and AddressID is the id field in both query and form

Its does not seem to run, am I doing something wrong?

 

[PHV]

I have entered the formula as follows
Where ?
BTW, is AddressID numeric ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

I have entered it into the control source of an unatached text box on the form. The addressID is numeric

 

[PHV]

You may add the following line of code in the AfterUpdate event procedure of the AddressID control:
Me![Name of unatached text box].ReQuery
You may also add this line in the Current event procedure of the form.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

i dont think its a requery problem. I have a macro set up to requery conected to a command button, so have run that. The field just has #Name? in it

 

[PHV]

Are the AddresID and the textbox controls in the same form ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

Yes there is a field called addressID in the same form as the txt box.

 

[PHV]

Have you tried the Expression builder to get rid of the #Name?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

yea i have tried that, but when it enters the field from the query it put it in like this
[TotalCost5]![SumOfTotal]

so the whole code becomes like this:

=DSum([TotalCost5]![SumOfTotal],[TotalCost5],[TotalCost5]![AddressID]=[AddressID])

Leaving of the NZ function for the minute.

 

[PHV]

OK, so try this:
=Nz(DSum("SumOfTotal","TotalCost5","TotalCost5.AddressID=" & AddressID),0)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[logopolis]

that did it, brilliant. Thank you very much.

John