Using dlookup with a field from the report as a "where condition"

[Guest]

I am try to call the following function in a textbox :
nz(dlookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] = "
&Report!customerid))
from a "company" header ("company grouping" on a report) . The issue I am
having is the "where condition" for customerid. I need to be able to lookup a
new escrow balance for each new company by feeding in the associated new
customerid.

The cutomerid field is part of the data environment of the report. The
function above gets the value from the first row but does not change as the
company groupings change.

Any thoughts?

Thanks

 

[Al Campagna]

mikeg,
If StartEscrow is related to CustomerID, then you should be able to include that field
in the query behind the report, rather than do a DLookup for it.
qryGetEscrowStartBal should be related to (linked) the main query table via CustomerID.
That would be the correct way to do it...

If you must DLookup...
=Nz(DLookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] =
Reports!YourReportName!CustomerID"
Didn't test, but that should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Pat Hartman \(MVP\)]

Try:
=nz(dlookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] = " &
customerid))

An alternative is to just join qryGetExcroStartBal to the query you are
using as the RecordSource for the report. That way, StartEscrow will be in
the recordset and can be bound to a control rather than retrieved via an
expression.

 

[Guest]

Al ,

Thanks for the help. Your're second way worked. But the Escrow amount for
the company grouping only prints out on the first page of my report. Other
companies are processed on more pages but hte Escrow amount is not there for
some reason. Any Thoughts?

Thanks,

Mike

mikeg,
If StartEscrow is related to CustomerID, then you should be able to include that field
in the query behind the report, rather than do a DLookup for it.
qryGetEscrowStartBal should be related to (linked) the main query table via CustomerID.
That would be the correct way to do it...

If you must DLookup...
=Nz(DLookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] =
Reports!YourReportName!CustomerID"
Didn't test, but that should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I am try to call the following function in a textbox :
nz(dlookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] = "
&Report!customerid))
from a "company" header ("company grouping" on a report) . The issue I am
having is the "where condition" for customerid. I need to be able to lookup a
new escrow balance for each new company by feeding in the associated new
customerid.

The cutomerid field is part of the data environment of the report. The
function above gets the value from the first row but does not change as the
company groupings change.

Any thoughts?

Thanks

 

[Guest]

Pat thanks

 

[Al Campagna]

I would still suggest using your GetEscroeStartBal query linked into the final query, to
provide the EscroeStartBal.
Using a Totals query, I would think a First of [StartEscroe] for each Customer would yield
the correct value.
Then it's a simple matter of Summing in any Group or Report Footer... no "slow" Dlookup...

You're call...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Pat thanks

I am try to call the following function in a textbox :
nz(dlookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] = "
&Report!customerid))
from a "company" header ("company grouping" on a report) . The issue I am
having is the "where condition" for customerid. I need to be able to lookup a
new escrow balance for each new company by feeding in the associated new
customerid.

The cutomerid field is part of the data environment of the report. The
function above gets the value from the first row but does not change as the
company groupings change.

Any thoughts?

Thanks

 

[Guest]

Thanks agan Al. Sounds like good advice.

I would still suggest using your GetEscroeStartBal query linked into the final query, to
provide the EscroeStartBal.
Using a Totals query, I would think a First of [StartEscroe] for each Customer would yield
the correct value.
Then it's a simple matter of Summing in any Group or Report Footer... no "slow" Dlookup...

You're call...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Pat thanks

I am try to call the following function in a textbox :
nz(dlookup("[StartEscrow]","qryGetEscrowStartBal", "[customerid] = "
&Report!customerid))
from a "company" header ("company grouping" on a report) . The issue I am
having is the "where condition" for customerid. I need to be able to lookup a
new escrow balance for each new company by feeding in the associated new
customerid.

The cutomerid field is part of the data environment of the report. The
function above gets the value from the first row but does not change as the
company groupings change.

Any thoughts?

Thanks