DLookup can't find a value

  • Thread starter Thread starter MJones
  • Start date Start date
M

MJones

Hi All,

An unbound object on a report asks to enter parameter value ClientID
and shows #Error on the report. I’ve tried:

=DLookUp("FNameF","tClient","ID = '" & [ClientID] & "'")
=DLookup("[FNameF]", "tClient", "[ID] = '" & [ClientID] & "'") – enter
parameter value ClientID (#Error)

ID in tClient table is an autonumber
ClientID in the tInv table is a number
FNameF is text

Any ideas would be great!

Michele
 
If ID is a number you don't need the '..'

=DLookUp("FNameF","tClient","ID = " & [ClientID])

Peterhttp://access.xps350.com/
Click to expand...

Hi,

I tried your suggested syntax and still get the same #ERROR asking for
the ClientID parameter.

I suspect this query is the problem. ClientID is in the tInv table,
which is included in the query in the rInv report's record source like
this:

SELECT tInv.AmountRec, tInv.InvNo, tInv.InvDate, tInv.InvNote,
tInv.ClassCode, tInv.PaymentMethod, tInv.ClassDates, tInv.Terms,
tInv.DueDate, tInventoryTransactions.ProductCode,
tInventoryTransactions.UnitsSold, tInv.InvDtlNo, tInv.ReceiptNo,
tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment, tClient.ID,
tClient.FNameF, tClient.FNameC, tClient.LName, tClient.Address1,
tClient.Address2, tClient.City, tClient.Prov, tClient.Country,
tClient.PCode, tClient.PhoneH, tClient.PhoneB, tClient.PhoneC,
tClient.EmailB, tClient.EmailP, tClient.PhoneExtB,
tProduct.ProductCode, tProduct.ProductDescription, tProduct.UnitPrice
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
(tClient.ID = tInv.PayerID) AND (tClient.ID = tInv.ClientID)) INNER
JOIN tClass ON tInv.ClassCode = tClass.ClassCode) ON
tInventoryTransactions.InvoiceID = tInv.InvNo) INNER JOIN tProduct ON
tInventoryTransactions.ProductCode = tProduct.ProductCode
WHERE (((tClient.ID)=[tInv].[PayerID] Or (tClient.ID)=[tInv].
[ClientID]));

I read information on queries, but struggle to understand the words
that I'm reading. Because of this, I tend to throw anything in that
might be needed and hope it works (I'm in the process of reading
Access 2003 for dummies). The types of joins baffle me.

The real problem to solve is this. tClient table has name, address,
email type information with ID auto number key. Imagine an invoice
with two tClient.ID's associated to it like a PayerID and a ShipperID,
which are both the same as one of the tClient.IDs. The PayerID and
ShipperID are collected in fInv form and stored in tInv table - one
for each invoice. Now, rInv report should display full contact
information from the tClient table (name, address, etc.) where the
PayerID matches a tClient.ID and the ShipperID matches the same or
different tClient.ID.

This is the real dilemma I've been struggling to solve all week. I'm
wondering if I need separate tables for shipper and payer contact
information. It seems like duplicate work that way.

If anyone can help, they will be my hero. Thanks again,

Michele
 
If ID is a number you don't need the '..'
Click to expand...
=DLookUp("FNameF","tClient","ID = " & [ClientID])
Click to expand...
Peterhttp://access.xps350.com/
Click to expand...

Hi,

I tried your suggested syntax and still get the same #ERROR asking for
the ClientID parameter.

I suspect this query is the problem.  ClientID is in the tInv table,
which is included in the query in the rInv report's record source like
this:

SELECT tInv.AmountRec, tInv.InvNo, tInv.InvDate, tInv.InvNote,
tInv.ClassCode, tInv.PaymentMethod, tInv.ClassDates, tInv.Terms,
tInv.DueDate, tInventoryTransactions.ProductCode,
tInventoryTransactions.UnitsSold, tInv.InvDtlNo, tInv.ReceiptNo,
tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment, tClient.ID,
tClient.FNameF, tClient.FNameC, tClient.LName, tClient.Address1,
tClient.Address2, tClient.City, tClient.Prov, tClient.Country,
tClient.PCode, tClient.PhoneH, tClient.PhoneB, tClient.PhoneC,
tClient.EmailB, tClient.EmailP, tClient.PhoneExtB,
tProduct.ProductCode, tProduct.ProductDescription, tProduct.UnitPrice
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
(tClient.ID = tInv.PayerID) AND (tClient.ID = tInv.ClientID)) INNER
JOIN tClass ON tInv.ClassCode = tClass.ClassCode) ON
tInventoryTransactions.InvoiceID = tInv.InvNo) INNER JOIN tProduct ON
tInventoryTransactions.ProductCode = tProduct.ProductCode
WHERE (((tClient.ID)=[tInv].[PayerID] Or (tClient.ID)=[tInv].
[ClientID]));

I read information on queries, but struggle to understand the words
that I'm reading.  Because of this, I tend to throw anything in that
might be needed and hope it works (I'm in the process of reading
Access 2003 for dummies).  The types of joins baffle me.

The real problem to solve is this.  tClient table has name, address,
email type information with ID auto number key.  Imagine an invoice
with two tClient.ID's associated to it like a PayerID and a ShipperID,
which are both the same as one of the tClient.IDs.  The PayerID and
ShipperID are collected in fInv form and stored in tInv table - one
for each invoice.  Now, rInv report should display full contact
information from the tClient table (name, address, etc.) where the
PayerID matches a tClient.ID and the ShipperID matches the same or
different tClient.ID.

This is the real dilemma I've been struggling to solve all week.  I'm
wondering if I need separate tables for shipper and payer contact
information.  It seems like duplicate work that way.

If anyone can help, they will be my hero.  Thanks again,

Michele
Click to expand...

I tried a select statement as the object source:

=(SELECT [tClient].[FNameF] FROM tClient WHERE [tClient].[ID] = [tInv].
[ClientID];)

and get #NAME?

If I take the outer most brackets out it says 'Check the subquery's
syntax and enclose the subquery in parentheses'. Not knowing which
part is the subquery, I also tried:

=SELECT [tClient].[FNameF] FROM tClient WHERE ([tClient].[ID] = [tInv].
[ClientID]);
=SELECT [tClient].[FNameF] FROM (tClient WHERE [tClient].[ID] = [tInv].
[ClientID]);

with the same syntax error.

I've tried so many things that I think my whole approach must be
wrong.
 
ClientID is not one of the selected fields in the query. So the DLookUp can not find it. Add it to the selected fields of the query.

Peter
 
I think your query should look like:

SELECT tINV.AmountRec, Payer.LName, Payer.City, Shipper.LName, Shipper.City
FROM (tClient AS Payer INNER JOIN tINV ON Payer.ClientID = tINV.PayerID) INNER JOIN tClient AS Shipper ON tINV.ShipperID = Shipper.ClientID;

Note that since you are using tClient twice (as payer and as shipper), you need to assign alternative names to table to tell the two rolls apart in the query. You don't have to alter your table desifgn for it.

I did not use all fields. Just to give you an idea.

Peter
 
Forgot to mention: no need for DLookUp(s). All data is (can be) in the query.

Peter
Click to expand...

Hi Peter,

Knowing you're still looking at it, I'm trying to understand your
first suggestion. I didn't know about the AS option. Okay, no
DLookUp. I think your suggested query goes in the object on the
report where I'm trying to pull in the person's name (FNameF) from
tClient table where the ID matches ClientID in the tInv table, not in
the rInv report's record source.

Right now, the tClient table should has fields like:

ID
FNameF
LName
Address1
Address2
City
etc.

These clients can be bill to and/or ship to. The tInv table only has
ClientID (ship to) and PayerID numbers that match to the ID number in
the tClient table. There is no other place that knows if a name or
address is a bill to or a ship to. It looks like you're saying that
tInv table (a record for each invoice) should have more than just
these two ClientID and PayerID numbers; that I should have a
PayerName, PayerAddress, ShipperName, ShipperAddress, etc.

I'll await your 'working out' with much appreciation. My program is
done with the exception of this last detail.

Michele
 
Michele,

Stop waiting! The query with the aliases (AS...) is the solution I worked out.

The query is the record source for the report.


Peter
 
Michele,

Stop waiting! The query with the aliases (AS...) is the solution I workedout.

The query is the record source for the report.

Peter
Click to expand...

Thank you very much. Greatly appreciated!
 
Forgot to mention, no need for dlookups. All data is (can be) in the query.

Peter
Click to expand...

FYI - I solved the real problem (bill to and ship to on same report)
by making a subform and separate table for the ship to client info.
 
Back
Top