Using Dlookup Or RecordSet to Return Multiple Values

Question

I'm trying to calculate a FIFO auditing analysis of inventory control. This is all done in MS Access 2010.

Unit 1 is added to inventory Today. Unit 2 is added Tomorrow.

Next week when a unit is selected for consumption, ideally we are using Unit 1, but if not, I'm building a query to identify this. So far I've gotten it to work to an extent using a DLookup function.

However, I need to identify which specific older Units were impacted by the failure to do FIFO. That's where DLookup Fails.

Unit #            In Date              Out Date
A                 1/1/13                1/14/13
B                 1/1/13                1/14/13
C                 1/4/13                1/13/13
D                 1/4/13                1/13/13

The above would result in my query spitting out Unit C twice and unit D twice, which is fine. However, each record in the query returns only Unit A when using Dlookup. That's because Dlookup is designed to only find the first value that matches the criteria, not all records.

For example:

Inefficiently Used Unit                   Overlooked Unit
C                                              A
C                                              A 
D                                              A 
D                                              A

This is what that DLookup function looks like:

DLookUp("[Container]","Container Efficiency","[Carrier]='" & [Carrier] & "' And [Container Type]='" & [Container Type] & "' And [In Date] < #" & [In Date] & "# And [Out Date] > #" & [Out Date] & "#"))

I need to design it so the criteria produces multiple values.

Inefficiently Used Unit                   Overlooked Unit
C                                              A
C                                              B

I did research and it appears using Recordsets and VBA would be the way to go. I'm playing with this now (having some trouble replicating the syntax and am considering using FindFirst/FindNext with criteria, then looping), but am getting conflicting opinions on if this is possible or how to go about writing this code.

HK1 HK1 · Accepted Answer · 2013-09-25T22:55:15

Use DLookup when you want to return a single value from a single field in a single row. Use DCount when you want to return a count of records meeting your criteria.

If you can't imagine making it work with either one then you might need to write your own function and use a DAO recordset (or ADO if you prefer that for some reason).

Your question seems to be missing some important information. Do you want a count or do you want to be able to see the actual matching rows? No matter what the answer is, your question really has a lot to do with your table structure (the design of your database) and SQL. Without knowing the design of your tables, it's almost impossible to give you SQL that's going to work.

Your last two diagrams are somewhat misleading. Do you expect your query to return two rows, showing one type of units in the first column and another in the second column? If so, you will probably need to use a cross tab query. Standard queries can indeed do grouping but they can't break variations within a single field into separate columns.

Once again, you might try making a query that does what you need and post the SQL on your question with some description of what you're really looking for. Will the data be in a report or form? How do you want it displayed: grouped, counted, individual?

Using Dlookup Or RecordSet to Return Multiple Values

1 Answers