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

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

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."


"mikeg" wrote in message
...
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

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.

"mikeg" wrote in message
...
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 ,

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

"Al Campagna" wrote:

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."


"mikeg" wrote in message
...
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

Pat thanks

"mikeg" wrote:

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

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."


"mikeg" wrote in message
...
Pat thanks

"mikeg" wrote:

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

Thanks agan Al. Sounds like good advice.

"Al Campagna" wrote:

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."


"mikeg" wrote in message
...
Pat thanks

"mikeg" wrote:

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