Query in a row source of a listbox, dlookup function.

jeffmoseler

Well-known Member
Joined
Jul 16, 2004
Messages
540
I have this dlookup in a query field and it is not working.
Code:
=DLookup ( "ItemNum" , "ItemList" , "ItemListID =" & [ItemList]![ItemKey])

I know it is just a matter of syntax, but I can't figure out what I have done wrong. The error keeps telling me it can't find "ItemListID". This query is in the row source of a listbox by the way, not in the queries section of the database. I have a feeling that has something to do with it since the query doesn't really have a name.

Also here is the SQL view:

SELECT RunSheetTable.RunSheetID, RunSheetTable.OrderQueryItemID, ItemListID, DLookUp("ItemNum","ItemList","ItemListID =" & [ItemList]![ItemKey]) AS Expr1
FROM ItemList INNER JOIN (RunSheetTable INNER JOIN OrderDataQuery ON RunSheetTable.OrderQueryItemID = OrderDataQuery.Key) ON (RunSheetTable.ItemListID = ItemList.ItemKey) AND (ItemList.ItemNum = OrderDataQuery.Item);

Thanks for the help!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi Mose,

Have you verified that you do in fact have a field named ItemListID in your ItemList table and that you haven't misspelled it somehow?

Also, looking at your SQL I'm not sure I understand what the dlookup is doing for you... you are already including the itemList table in your query so why not just grab the ItemNum field directly, instead of the DLOOKUP?

hth,
Giacomo
 
Upvote 0
I have verified that everything is spelled correctly.

It is hard to explain why I need to do this. Suffice it to say that when I try to grab the itemNum field directly it leaves all the 0 in that field out. So I end up with an upended list. Let me try to explain, maybe you have a better idea:

I have a table where two Key's are being entered in two different column. It will either have a key from one table in field one or a Key from another table in Field two. IE:

Code:
OrderQueryItemID     ItemListID
23                             0
54                             0
3                              0
0                              13
0                              87
59                             0
I need the query to display in column 3 the appropriate Item for the ID weather it is in column 1 or column 2. When I try to grab the ItemNum in OrderDataQuery cancels out all the 0's, when I try to grab for both ItemNum's it cancels out the zero's (I'm assuming) in both columns and leaves me with nothing in the query.

Ultimately, I need it to look like this:
Code:
OrderQueryItemID     ItemListID            ItemNum
23                             0                  11111
54                             0                  22222
3                              0                  33333
0                              13                 44444
0                              87                 55555
59                             0                  66666

My idea was to do a IIF function with two DLOOKUP functions. I'm fairly confident in getting my IIF function to work, once I figure out the DLOOKUP function. I am really struggling trying to learn the syntax of a DLOOKUP.

Let me know what you think.
 
Upvote 0
I believe the problem you're having is that you're trying to use the DLOOKUP function when you should not be using it (your syntax is correct however). If you can provide some sample data for RunSheetTable, and ItemList, plus the SQL for OrderDataQuery I can help you out.

hth,
Giacomo
 
Upvote 0
OK, I'll try. It's kind of complicated, but here goes:

The query I am trying to do this in is actually located in a listbox rowsource property. I haven't quite figured out how to refer to the query since there really isn't a name assigned to it, so I will just refer to it as listboxquery.

Sample Data:
Code:
RunSheetTable
RunSheetID     OrderQueryItemID               ItemListID
1                     45                            0
2                     12                            0
3                     0                             3
4                     77                            0
5                     0                             64
6                     0                             22

OrderDataQuery
Key     Item
45      12456
12      32552
77      14785

ItemListTable
ItemKey        ItemNum
3                28556
64               12114
22               98521

The result in listboxquery needs to be:

RunSheetID     OrederQueryItemID       ItemListID                  LookupField
1                       45                     0                      12456
2                       12                     0                      32552
3                       0                      3                      28556
4                       77                     0                      14785
5                       0                      64                     12114
6                       0                      22                     98521
The last column in the listboxquery is basically determining wheather it should lookup the item number from the OrderQueryItemID column or the ItemListID column and listing the appropriate Item Number.

Man, I hope this makes sense.
 
Upvote 0
OK, thanks that makes a lot more sense now. Try this query I think it will work well for you...

Code:
SELECT 
runSheetTable.RunSheetID
, runSheetTable.OrderQueryItemID
, runSheetTable.ItemListID
, nz([Item],[ItemNum]) AS LookupField
FROM 
ItemListTable 
RIGHT JOIN 
( OrderDataQuery 
  RIGHT JOIN runSheetTable 
  ON OrderDataQuery.Key = runSheetTable.OrderQueryItemID 
) 
ON ItemListTable.ItemKey = runSheetTable.ItemListID;

hth,
Giacomo
 
Upvote 0
That is so cool. I can't believe it was that easy! After all that work. Thank you so much, this is going to be awesome!

I just wish I had some clue as to how you did that.

What does this actually mean?

LookupField: nz([Item],[ItemNum])
 
Upvote 0
the nz() function stands for null zero, all it does is swap out a null value with the second value. So whenever it finds a null [item] it returns the [itemNum] instead.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top