Dlookup criteria

[melika]

Hi,

I have a reference table that has a Division field and a Item field which lists all possible items I sell and what division they fall into. example below:

Div Item
Cars 100
Cars 101
Bikes 200
Bikes 201

I have another table that has the item sold [ItemNo], quantity and other info.

From this table, I made a query to pull the item and quantity. Then I wanted to determine the division using dlookup.

Division: Dlookup( "[Div]", "Ref Table", "[Item]=" & [ItemNo]) and this works. If I have item 100, it returns cars. Item 200 it returns bikes.

However, I also want it to filter so it only returns the items from one division or the other. Let's say I only want to see Bikes. I tried putting = " Bikes" in the criteria and it freaked out on me. Then I tried putting it in the Dlookup code...

Division: Dlookup("[Div]", "Ref Table", "[Item]=" & [ItemNo] & " AND "[Div]", "Ref Table" = " & "Bikes") but all I get is an error for division.

Any suggestions? Thanks!

 

[WayneRyan]

Melika,

Punctuation, note the single-quotes.

Division: Dlookup("[Div]", "Ref Table", "[Item] = " & [ItemNo] & " AND [Div] = 'Bikes'")

But, why look up Division, when you know it.

Wayne

 

[Simon_MT]

If [ItemNo] = [Item] then you could simply create an SQL Statement with a Join.

Simon