Help with the Dlookup function

[Grace]

I am trying to add a field to a report using the Dlookup
function. Below is the syntax I currently have:

=Sum(DLookUp("[HONattyWght]","Ratios Table","[Ratios
Table]![RegionNumber]=[RegionNumber]"))

There are several HONattyWght values for each RegionNumber
in the Ratios table. I would like to retrieve and sum the
HONattyWght values for each region and place this value on
the report. The value I am getting using the above syntax
sums ALL the values for the HONattyWght field and does not
group the values by regin.

Your help is greatly appreciated.

Grace

 

[Duane Hookom]

There has to be a better method using a totals query and joining it in your
record source. However, try:
If RegionNumber is numeric:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=" & [RegionNumber]))

If RegionNumber is text:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=""" & [RegionNumber] & """"))

 

[Grace]

Thanks, but that doesn't seem to do it.

After researching DLookup a bit more, turns out that
funciton can only retrieve one value from a field. If
mutliple values exist, it only retrieves the first one.
Do you know if there is a function that will retrieve a
set of values?

I will think about using a query.

Thanks, Grace

-----Original Message-----
There has to be a better method using a totals query and joining it in your
record source. However, try:
If RegionNumber is numeric:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=" & [RegionNumber]))

If RegionNumber is text:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=""" & [RegionNumber] & """"))

--
Duane Hookom
MS Access MVP

I am trying to add a field to a report using the Dlookup
function. Below is the syntax I currently have:

=Sum(DLookUp("[HONattyWght]","Ratios Table","[Ratios
Table]![RegionNumber]=[RegionNumber]"))

There are several HONattyWght values for each RegionNumber
in the Ratios table. I would like to retrieve and sum the
HONattyWght values for each region and place this value on
the report. The value I am getting using the above syntax
sums ALL the values for the HONattyWght field and does not
group the values by regin.

Your help is greatly appreciated.

Grace

.

 

[Fredg]

Grace,
Use DSUM()

If RegionNumber is numeric:
=DSum("[HONattyWght]", "Ratios Table","[RegionNumber]=" & [RegionNumber])

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Thanks, but that doesn't seem to do it.

After researching DLookup a bit more, turns out that
funciton can only retrieve one value from a field. If
mutliple values exist, it only retrieves the first one.
Do you know if there is a function that will retrieve a
set of values?

I will think about using a query.

Thanks, Grace

-----Original Message-----
There has to be a better method using a totals query and joining it in your
record source. However, try:
If RegionNumber is numeric:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=" & [RegionNumber]))

If RegionNumber is text:
=Sum(DLookUp("[HONattyWght]", "Ratios Table",
"[RatiosTable]![RegionNumber]=""" & [RegionNumber] & """"))

--
Duane Hookom
MS Access MVP

I am trying to add a field to a report using the Dlookup
function. Below is the syntax I currently have:

=Sum(DLookUp("[HONattyWght]","Ratios Table","[Ratios
Table]![RegionNumber]=[RegionNumber]"))

There are several HONattyWght values for each RegionNumber
in the Ratios table. I would like to retrieve and sum the
HONattyWght values for each region and place this value on
the report. The value I am getting using the above syntax
sums ALL the values for the HONattyWght field and does not
group the values by regin.

Your help is greatly appreciated.

Grace

.