Dlookup not returning values or getting error messages or asking for parameters

[philp]

Returning novice user



I need to prove the principle of DLookUp in a small example database before using it elsewhere.

I have set up two tables.

tbl_Name
contains two fields of standing data
Number
(alphanumeric, aannnn where a = alphabetic and n = numeric character)

Name
containing a text string

tlkp_Test
contains two fields
LocNumber
where the user has a choice from a drop down list populated from tbl_Name field Number (this part is working!)

LocName
where the DLookUp should retrieve the Name value in tbl_Name that matches the number the user chose in tlkp_LocName field LocNumber

IncorrectDLookUp code

SELECT DLookUp([tbl_Name]![Name],[tlkp_Test]![LocNumber],[tbl_Name]![Number]) AS Expr1;

I have tried many sources of guidance, help and browsed this forum, but have to confess myself beaten. Various attempts have resulted in a request for parameters, error messages about incorrectly defined data sources or blank refusal to work.
I'd be really grateful for your help so that I can make progress with the work that depends on this.

[R_Badger]

You dont really want to do it that way, at least it seems you are trying to merge a table, SQL and a dlookup!

What you need here is a query. More specifically a join query, (more here)

The SQL should be something along the lines of:

SELECT * FROM tbl_Name JOIN tlkp_Test on tlkp_test.locnumber=tbl_Name

(I add this for completeness) try not to use reserved words in field names name and number are two of them! A full list is available here

Also, using this method the tlkp_Test.Locname field is redundant, (in accordance with normalisation, more here)

[orange]

Further to R_Badger's comments:

Your DLookup syntax is incorrect.
Let Google help you find these general definitions, examples etc.

techonthenet.com is a very good source, as is w3schools

http://www.techonthenet.com/access/f...in/dlookup.php

[philp]

Dear Colleagues
Thanks for your prompt responses. I will re-visit the problem with your advice in mind in the next couple of days and let you know how things went.