Help with the Dlookup function

  • Thread starter Thread starter Grace
  • Start date Start date
G

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
 
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] & """"))
 
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


Grace said:
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
Click to expand...


.
Click to expand...
 
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.


Grace said:
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


Grace said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top