Dlookup criteria in report

[Guest]

I have a report with almost 250 unbound fields that uses Dlookups and I am
trying to use a field on the report as part of the criteria in the Dlookup.
The field is named fldIsuNm. Any help would be appreciated.

DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = fldIsuNm")

 

[Guest]

I would have to expect there is a much better solution than 250 resource
hogging domain aggregate functions...

However, try:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = " & fldIsuNm)

Since the field name contains "Nm", I am assuming it is numeric. If it isn't
you will need to use:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = """ & fldIsuNm & """")

 

[Guest]

Duane,

Thanks for the assistance. I had to use the aggregate functions because I
could never figure out how to make a series of subqueries to make the rows
appear in my crosstab query when there was no data. Now all I need to do is
use the Dateadd() function to make the years dynamic. What is the proper use
of quotation for the Dateadd portion in the following:

DLookUp("ShareTotal","tblXHolding","[ExpYr] = CStr(Year(DateAdd("""& yyyy
&""",1,Date()))) AND [ExpMth] = 1 AND[AssetNm] = 'Government' And [IsuNm] =
""" & [fldIsuNm] & """")

I would have to expect there is a much better solution than 250 resource
hogging domain aggregate functions...

However, try:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = " & fldIsuNm)

Since the field name contains "Nm", I am assuming it is numeric. If it isn't
you will need to use:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = """ & fldIsuNm & """")

--
Duane Hookom
Microsoft Access MVP

I have a report with almost 250 unbound fields that uses Dlookups and I am
trying to use a field on the report as part of the criteria in the Dlookup.
The field is named fldIsuNm. Any help would be appreciated.

DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = fldIsuNm")

 

[Guest]

You get "rows" to appear by joining the crosstab results to a table that
contains all of the row values. Set the join to include all of the values
from the row table.

DLookUp("ShareTotal","tblXHolding",
"[ExpYr] = CStr(Year(DateAdd('yyyy',1,Date()))) AND [ExpMth] = 1 AND
[AssetNm] = 'Government' And [IsuNm] = """ & [fldIsuNm] & """")
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the assistance. I had to use the aggregate functions because I
could never figure out how to make a series of subqueries to make the rows
appear in my crosstab query when there was no data. Now all I need to do is
use the Dateadd() function to make the years dynamic. What is the proper use
of quotation for the Dateadd portion in the following:

DLookUp("ShareTotal","tblXHolding","[ExpYr] = CStr(Year(DateAdd("""& yyyy
&""",1,Date()))) AND [ExpMth] = 1 AND[AssetNm] = 'Government' And [IsuNm] =
""" & [fldIsuNm] & """")

I would have to expect there is a much better solution than 250 resource
hogging domain aggregate functions...

However, try:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = " & fldIsuNm)

Since the field name contains "Nm", I am assuming it is numeric. If it isn't
you will need to use:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = """ & fldIsuNm & """")

--
Duane Hookom
Microsoft Access MVP

I have a report with almost 250 unbound fields that uses Dlookups and I am
trying to use a field on the report as part of the criteria in the Dlookup.
The field is named fldIsuNm. Any help would be appreciated.

DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = fldIsuNm")

 

[Guest]

Duane,

Thanks for all the help. I will let you know if I get the subquery to work.
However, the report works like a charm and is not slow.

Sincerely,
Shaun

You get "rows" to appear by joining the crosstab results to a table that
contains all of the row values. Set the join to include all of the values
from the row table.

DLookUp("ShareTotal","tblXHolding",
"[ExpYr] = CStr(Year(DateAdd('yyyy',1,Date()))) AND [ExpMth] = 1 AND
[AssetNm] = 'Government' And [IsuNm] = """ & [fldIsuNm] & """")
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for the assistance. I had to use the aggregate functions because I
could never figure out how to make a series of subqueries to make the rows
appear in my crosstab query when there was no data. Now all I need to do is
use the Dateadd() function to make the years dynamic. What is the proper use
of quotation for the Dateadd portion in the following:

DLookUp("ShareTotal","tblXHolding","[ExpYr] = CStr(Year(DateAdd("""& yyyy
&""",1,Date()))) AND [ExpMth] = 1 AND[AssetNm] = 'Government' And [IsuNm] =
""" & [fldIsuNm] & """")

I would have to expect there is a much better solution than 250 resource
hogging domain aggregate functions...

However, try:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = " & fldIsuNm)

Since the field name contains "Nm", I am assuming it is numeric. If it isn't
you will need to use:
=DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = """ & fldIsuNm & """")

--
Duane Hookom
Microsoft Access MVP


:

I have a report with almost 250 unbound fields that uses Dlookups and I am
trying to use a field on the report as part of the criteria in the Dlookup.
The field is named fldIsuNm. Any help would be appreciated.

DLookUp("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND
[ExpMth] = 2 AND[AssetNm] = 'Corporate' And [IsuNm] = fldIsuNm")