DLookUp Query across multiple tables

[karldou]

Hi,



I have researched the use of DLookUp across the Internet and this forum and I am still struggling to get my query to work.

Firstly I have an Access 2007 database with 2 Tables:
-tblLvl
-ID (AutoNum)
-Levels (Text)
-NumValue (Num)

-tblData
-ID (AutoNum)
-DataColl1 (Text)
-DataColl2 (Text)

I also have a query which is where the problem is.

What I am trying to do is to run a Query for the fields DataColl1 and DataColl2 in the tblData table.

The fields DataColl1 and DataColl2 use a number and a letter pattern like "4c", "4b", "4a", "5c", "5b", "5a" etc...

In the tblLvl table there is a Levels field which contains all the possible combinations of the above, with a numerical value field called NumValue next to it.

What I want the query to do is to read the value selected in the DataColl1 field from tblData table and find the relevant NumValue from the tblLvl table based on a matching code in the Levels field of tblLvl.

I have created a query which contains 3 fields, DataColl1(tblData), DataColl2(tblData), both with no criteria, and the expression below in the 3rd field:

Expr1: DLookUp([NumValue],"tblLvl","DataColl1 = " & [tblLvl]![Levels])

When i run the query all i get back is the list of data from the first 2 fields, and the 3rd field is just blank.

Can anyone give me an idea of where I am going wrong?

I have attached the database zipped up to demonstrate what i am talking about

Many thanks for all your help.

Karl

[orange]

Your syntax for DLookup is incorrect.
see http://www.techonthenet.com/access/f...in/dlookup.php

It also sounds like your table(tblData) is not normalized.

What are you trying to do?

[karldou]

Your syntax for DLookup is incorrect.
see http://www.techonthenet.com/access/f...in/dlookup.php

It also sounds like your table(tblData) is not normalized.

What are you trying to do?

thanks for the quick reply, basically what i am trying to do is to recreate what i already do in an Excel spreadsheet. I use a VLOOKUP to find the matching code i.e. 4c in a list on a different worksheet. next to the correct code there will be a numerical value i.e. 13. the function will then return the value 13 in the specified cell due to the code 4c.

I am wanting to recreate this in a database, so on tblLvl will be the lookup list of codes and matching numerical values. Then on the tblData table the code present will be matched with the numerical value from tblLvl and the two values will be displayed in the query.

Does that make sense?

thanks
Karl