DLookup

[dieselrocks]

Stewart/NeoPa,

I'm having a little trouble getting the syntax correct on a Dlookup. I have a form with an ID field on it. I want to match that ID to another field in a table. Once it finds a match, I want to retrieve the value of a different field in the same table. Here is the code:

=DLookUp("[Order Received]",("Orders"),"forms![mainform]![orderID]="[EbayOrder], [orderID]"

To clarify, I want to use the value of the order id field on the mainform to compare to the orderID in the EbayOrder table. If it finds a match, I want it to return the value of order received in the orders table.

[NeoPa]

I have split this question away from http://bytes.com/topic/access/answer...ill-not-update.

Please do not post your question in other members' threads as that is Thread Hihacking and is outlawed on this site. I appreciate this is your first post so nothing official to follow. Just take a glance at How to Ask a Question please, to ensure you understand some of the basic dos and don'ts of the site.

-Administrator.

PS. Welcome to Bytes!

[NeoPa]

Let's forget DLookup() for the moment. The more usual way of providing that functionality is to build the form on a query where the two tables are already linked so that all the data is already available. Not only is it fundamentally easier, it also works more efficiently (Generally not noticeable in simple forms mind).

[Stewart Ross]

Hi. As NeoPa says, you will need a query which joins the tables you want to look up. DLookup is intended to look up a single value in a table or query. What it cannot do is look up a value in one table and return a value from another as you request, unless the source is itself a query which joins the tables concerned.

As mentioned, DLookup cannot join tables for you - you must devise a query to join the tables. That query could be the one you use as the recordsource of your form, in which case you will not need to use DLookup at all.

If you do use DLookup, fed by a suitable query containing the joined tables, you have to get the syntax right - your WHERE part is incorrect at present. I show an example of the correct syntax for your DLookup statement below, basing the lookup not on a table but a query called qryOrders, and assuming that OrderID is a numeric value:

=DLookUp("[Order Received]", "qryOrders", "[orderID] = " & [orderID])

However, I agree entirely with NeoPa that you should get the query approach right first before you devote any more time to DLookup.

-Stewart

[dieselrocks]

Thanks for the insight and sorry about the posting error. I read the inital post and thought it might be an easy follow up question.

Back to my issue. I understand your directions. I created a seperate query named qryOrder Received to obtain the information needed. I plan to then use the Dlookup to obtain the corresponding value. However, I'm still struggling with the code. First, the record source for the order form is not order recieved so I'm guessing I need to specify the form name in the criteria portion of the Dlookup. Secondly, orderid is text. Does that mean it should be in quotes?

=DLookUp("[Order Received]", "qryOrder received", "[orderID] = " forms! [orderform]! ["orderID"])

[dieselrocks]

I think I have the code figured, but I only get #error for results. Here is my code:

=DLookUp("[order received]","qryorder received","[order id] = " & Forms!mainform!order id)

Order ID is text. Does it matter that on the form, orderID has an input mask? I believe the value is stored without it.

[Dan2kx]

=DLookUp("order received","qryorder received","[order id] = [Forms]![mainform]![order id])

try that

things can get a bit unpredictable when you have spaces in names.

[Stewart Ross]

The #Error usually means that one of the names you are supplying within the function is incorrect or not recognised by Access. Dan 2K is correct about spaces - if there are spaces in the name of a control you must bracket the part with the spaces, [like this], or an error will occur.

You mention that Order ID is text. This changes the where clause, as the value you are comparing has to be supplied within single quotes as follows:

=DLookUp("[order received]","[qryorder received]","[order id] = '" & Forms!mainform![order id] & "'")

If the form control you are comparing is on the same form as the unbound control which is doing the DLookup you do not need to supply the Forms!Mainform part of the reference at all. Note that the [order id] on the left side of the expression is actually the name of the field within the query [QryOrder Received] which DLookup is running. The [Order ID] on the right is the name of the control on our form in which the value you are comparing is stored. The names are the same but they are not the same entities.

=DLookUp("[order received]","[qryorder received]","[order id] = '" & [order id] & "'")

@Dan2K: referring to form controls as part of the WHERE clause may not work; the query engine in some cases will supply the necessary value from the form, but whether or not DLookup itself will allow this in all cases is debatable. That is why the value of the control is normally supplied as part of the WHERE clause, concatenated in to the string using the ampersand operators.

-Stewart

[dieselrocks]

Thanks, but, no cigar....

I made the changes but once I click OK, access converts the code to this:

=DLookUp("order received","qryorder received","[order ID] = " & Forms!mainform!order id)

[Dan2kx]

i must admit i usually use " & me...... but i would use the other method (i posted) on the rare occasion, it eliminates the need for extra quotes/#'s (in my experience?)

Dan

[Stewart Ross]

Dieselrocks, no cigar or not perhaps you would actually use the syntax I suggested - where are the single quotes?? Where is the bracketing??

=DLookUp("[order received]","[qryorder received]","[order ID] = '" & Forms!mainform![order id] & "'")

@Dan2k the Me notation works in VBA code only.


-Stewart

[Dan2kx]

which is why i didnt suggest it :-)

actually i must of skipped some thread, didn't realise you was not in VBA

[Stewart Ross]

@Dan2kx
I guess this was an aside and not a suggestion then! -S

[dieselrocks]

Stewart,

Thanks for your wisdom...it WORKS!!!! Glad I found this web site....I'm sure I'll be back!!

p.s. thanks to you too Dan2Kx

[Stewart Ross]

@Dieselrocks - glad it's now working for you, and

Welcome to Bytes!

-Stewart

[NeoPa]

A separate (but interesting) question has been split off to http://bytes.com/topic/access/answer...alue-reference. Any interested parties can follow it there.