Dlookup issue

[Demosthenis]

Hello,

I am trying to seto up a Dlookup in a query.

My Dlookup field will retrieve data from a second query. The match criteria is the W field. This dlookup up process has to retrieve 24 different values from the second query (Query5). Unfortunatelly the function always brings the first value of the 2nd query for all the 24 fields.

My formula is the
Expr10: IIf([ph4]="Stock4";dLook Up("SumOfExpr2" ;"Query5";"[W]=" & "[Query5].[W]");[SumOfSell out]).

Is it something wrong with the formula or the Dlookup just works once!!!!

Many thanks

Demosthenis

[Stewart Ross]

Hi, and Welcome to Bytes!

Firstly, your filter value in the DLookup tries to filter on a string literal value that is the name of the field you are comparing against - which can't possibly be right (..."[W]=" & "[Query5].[W]"). It is clearly incorrect that you are trying to lookup something in Query5 where its own field [W] equals itself - the filter will not filter anything in these circumstances.

Removing the redundant ampersand from your filter you really just have
"[W]=[Query5].[W]"

and since it is Query5 that you are looking up you really have a filter that is just

"[W]=[W]"

As this will always evaluate as True (just as 1=1 is True, as indeed anything = anything is also True) there is no filter at all being applied.

In any event, DLookup can only return one value - that is its job.

I reckon you have not thought through what you are trying to achieve here, and you will need to rethink your approach.

-Stewart

[Demosthenis]

Hi, and Welcome to Bytes!

Firstly, your filter value in the DLookup tries to filter on a string literal value that is the name of the field you are comparing against - which can't possibly be right (..."[W]=" & "[Query5].[W]"). It is clearly incorrect that you are trying to lookup something in Query5 where its own field [W] equals itself - the filter will not filter anything in these circumstances.

Removing the redundant ampersand from your filter you really just have
"[W]=[Query5].[W]"

and since it is Query5 that you are looking up you really have a filter that is just

"[W]=[W]"

As this will always evaluate as True (just as 1=1 is True, as indeed anything = anything is also True) there is no filter at all being applied.

In any event, DLookup can only return one value - that is its job.

I reckon you have not thought through what you are trying to achieve here, and you will need to rethink your approach.

-Stewart

Many thanks for your quick reply.

It is clear now where the problem is. Is it possible in some way to define the logic

"[Query4].[W]=[Query5].[W]"

since this is what I am trying to achieve. - the formula lies in Query 4.

[Stewart Ross]

Forget DLookup - it is not what you need. As I mentioned, it can only return a single value. You need to join Query 4 and Query 5 at least on the W field. You may also need to join on other key fields common to both queries that you don't mention, as otherwise the resultant query will cause multiplication of rows in the joined dataset.

Easiest way to do this is using the Access query editor, which will simplify the process of creating the joins, selecting the fields etc.

I strongly advise you to use meaningful names for fields, queries and so on. Names such as W, Query4, Query5 don't help you to understand what any of these are for. Using such meaningless names will hamper you in trying to see how to develop properly-joined queries.

-Stewart