DLookup can't find a value

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
 
M

MJones

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
 
M

MJones

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

XPS350

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
 
X

XPS350

I did not read the "real" problem. Need to work out something. Will be back soon.


Peter
 
X

XPS350

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
 
M

MJones

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
 
X

XPS350

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
 
M

MJones

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!
 
M

MJones

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help 31
Report Group Footer DLookUp 0
Launching Report from Form Only Shows Report for First Record 5
Crosstab Query 0
Blank Fields in Report 2
Sum in Report Errors 3
Controlsource won't let Dlookup to enter 12
data appears in Layout View but not in Print Preview? 1

Top