DLookup

[Jishnu Surendran]

I have a function in a query;DLookUp("[Field1]","[Table2]","[Field2]="&[Field2]).

Field 1 = Value I want to retrieve in Table 2
Table 2 = Lookup table
Field 2 = Lookup value in Table 1 which is also in the corresponding row of Field 1 in Table 2

This function is returning an error.


Field 1 & 2 are text values.
Field 2 is a list created from Table 2.

Help me on this!

[ssanfu]

If (since) Field 1 & 2 are text values, you are going to need text delimiters.


What the DLookup is saying is "Find a record in Table2 where Field2 equals the specified value and return the value from Field1 in table2.

Lets say there is a form named "Form1" with a text box named "tbText2".
Then the DLookup would look like:

DLookUp("[Field1]","[Table2]","[Field2] = '" & Forms!Form1.tbText2 & "'")

You have to concatenate the value to the WHERE clause in the DLookup.

Field 2 = Lookup value in Table 1 which is also in the corresponding row of Field 1 in Table 2

Don't really understand what you mean here

Maybe

DLookUp("[Field1]","[Table2]","[Field2] = '" & NameOfTable1.field2 & "'")

If this doesn't make sense to you, post the SQL of the query and indicate which fields are to be used.......

[Minty]

99 times out of 100 you should be using a join in the query to find these values.

Add table 2 to your query and join the fields you are trying to match.

[NoellaG]

Hi,

I agree with Minty, it's a very bad idea to use Dlookup functions in a query. For each result record the query has to do a full table scan of the lookup table without any use of indexes, making the whole query very, very slow.
Better solve the query using joins.

[ssanfu]

I agree with Minty and NoellaG.... I my example was just to clarify why your DLookup didn't work.