DSum

[Eric]

Someone Please Help? I will really appreciate it.

I am trying to get the DSum function to work with multiple conditions.
I can get the following to work if I limit it to the first condition
in the criteria, (regarding current mapping), but when I add the
others, I get "#Error" in the [Avg Credited Rate] field.

The query I am running is named, "[Test Weighted Avg]".
[Current Mapping] is a string field, this field is never Null.
[Guarantee Period] is a number, sometimes this field is Null.
[DCA Type] is a number, sometimes this field is Null.
[DCA Period] is a number, sometimes this field is Null.

Here is my expression for [Avg Credited Rate]

Avg Credited Rate: [Rate]*[Amount]/DSum("[Amount]","[Test Weighted
Avg]","[Current Mapping] = '" & [Current Mapping] & "' AND [Guarantee
Period] = " & [Guarantee Period] & " AND [DCA Type] = " & [DCA Type] &
" AND [DCA Period] = " & [DCA Period])

After I get this to work, I want to enclose it all inside Sum() by
changing the query to a Totals query and Group By: [Current Mapping],
[Guarantee Period], [DCA Type], and [DCA Period]. This will give me a
weighted average for each unique group.

I've been struggling with this for a long time and I'm begging someone
to please help me!

Thanks very much,
Eric

 

[Dan Artuso]

Hi,
Is there any chance that DSum will be zero?
Also, what is the data type for the fields you are including in
the criteria?
Finally, if you absolutely can't get it to work, feel free to send me
a zipped copy of the db with only the relevant tables so I can
test it. Compact the database first to make it as small as possible,
I only have a dial up connection AND I live well outside of the city
so my highest speed is only ever 31 kbps.
Remove the NOSPAM from my address.

 

[Eric]

Dan,

Thanks for your response. If you are still out there I would
appreciate the help. I have tried sending you a test copy but keep
getting undeliverable errors for your e-mail address (with & without
the NoSpam).

I will answer your question regarding 0's the following way. I have
four fields that will define my groupings (see below for the
descriptions and datatypes). Some combinations of groupings may not be
present so the sum would be 0. But the query will not return these
values, correct? If the grouping combination exists, it will have a
positive value.

If you are still around, I would appreciate further help. I also
welcome any other comments regarding this.

Thanks,
Eric

Hi,
Is there any chance that DSum will be zero?
Also, what is the data type for the fields you are including in
the criteria?
Finally, if you absolutely can't get it to work, feel free to send me
a zipped copy of the db with only the relevant tables so I can
test it. Compact the database first to make it as small as possible,
I only have a dial up connection AND I live well outside of the city
so my highest speed is only ever 31 kbps.
Remove the NOSPAM from my address.

--
HTH
Dan Artuso, Access MVP

Someone Please Help? I will really appreciate it.

I am trying to get the DSum function to work with multiple conditions.
I can get the following to work if I limit it to the first condition
in the criteria, (regarding current mapping), but when I add the
others, I get "#Error" in the [Avg Credited Rate] field.

The query I am running is named, "[Test Weighted Avg]".
[Current Mapping] is a string field, this field is never Null.
[Guarantee Period] is a number, sometimes this field is Null.
[DCA Type] is a number, sometimes this field is Null.
[DCA Period] is a number, sometimes this field is Null.

Here is my expression for [Avg Credited Rate]

Avg Credited Rate: [Rate]*[Amount]/DSum("[Amount]","[Test Weighted
Avg]","[Current Mapping] = '" & [Current Mapping] & "' AND [Guarantee
Period] = " & [Guarantee Period] & " AND [DCA Type] = " & [DCA Type] &
" AND [DCA Period] = " & [DCA Period])

After I get this to work, I want to enclose it all inside Sum() by
changing the query to a Totals query and Group By: [Current Mapping],
[Guarantee Period], [DCA Type], and [DCA Period]. This will give me a
weighted average for each unique group.

I've been struggling with this for a long time and I'm begging someone
to please help me!

Thanks very much,
Eric