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])


Peter
http://access.xps350.com/

On Oct 31, 3:57*am, XPS350 <xps...@gmail.com> wrote:
> If ID is a number you don't need the '..'
>
> =DLookUp("FNameF","tClient","ID = " & [ClientID])
>
> Peterhttp://access.xps350.com/

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

On Oct 31, 9:03*am, MJones <mjones1...@gmail.com> wrote:
> On Oct 31, 3:57*am, XPS350 <xps...@gmail.com> wrote:
>
> > If ID is a number you don't need the '..'
>
> > =DLookUp("FNameF","tClient","ID = " & [ClientID])
>
> > Peterhttp://access.xps350.com/
>
> 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

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 did not read the "real" problem. Need to work out something. Will be back soon.


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

On Oct 31, 10:28*am, XPS350 <xps...@gmail.com> wrote:
> I did not read the "real" problem. Need to work out something. Will be back soon.
>
> Peter

Thank you so much!

Forgot to mention, no need for dlookups. All data is (can be) in the query.

Peter

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

Peter