code to determine correct dimensions

[mbar]

Hi-

I have products listed below:

itm	shipdim1	shipdim2	shipdim3
PROD10	28	13	12
PROD11	13	12	3
PROD12	25	25	18
PROD13	60	15	12
PROD14	25	8	7
PROD15	16	15	12
PROD16	25	8	7
PROD17	25	8	7
PROD18	44	14	12
PROD19	28	19	14
PROD20	25	25	18
PROD21	28	19	14
PROD22	37	8	7
PROD23	13	11	1
PROD24	16	15	12
PROD25	16	15	12
PROD26	32	21	18
PROD27	32	21	18
PROD28	41	19	10
PROD29	41	19	10
PROD30	22	6	32

I also have multiple boxes that these products need to fit into:

cardbid	dim1	dim2	dim3
A	58	15	12
B	56	25	18
C	48	36	18
D	47	32	23
E	44	15	12
F	42	32	25
G	37	8	7
H	32	21	18
I	30	15	12
J	30	28	5
K	28	13	12
L	28	19	14
M	28	28	11
N	25	8	7
P	25	25	18
Q	16	13	3
R	16	15	12

I need create a function that uses the smallest box possible, where shipdim1 doesn not exceed dim1, shipdim2 does not exceed dim2, ect.. Ultimately, I need to find the [cardbid] value that is the best match.

For example, the StBestBox variable for PROD10 would be carbid=I

I'm assuming this can be achieved using Dlookup and Dmin?

Please help!!

[June7]

It gets more complicated than that. I can get this far for you. This parameterized query will return records that meet minimum dimensions. In the case of PROD10, two boxes match - I and K.

SELECT cardbid, dim1, dim2, dim3
FROM Table1
WHERE (((dim1) Between [d1] And DMin("dim1","table1","dim1>" & [d1])) AND ((dim2) Between [d2] And DMin("dim2","table1","dim2>" & [d2])) AND ((dim3) Between [d3] And DMin("dim3","table1","dim3>" & [d3])));

[mbar]

Thank you. I'll try this out. I suppose I could calculate the volume and choose the lesser amount if 2 if the query returns 2 or more results...

It gets more complicated than that. I can get this far for you. This parameterized query will return records that meet minimum dimensions. In the case of PROD10, two boxes match - I and K.

SELECT cardbid, dim1, dim2, dim3
FROM Table1
WHERE (((dim1) Between [d1] And DMin("dim1","table1","dim1>" & [d1])) AND ((dim2) Between [d2] And DMin("dim2","table1","dim2>" & [d2])) AND ((dim3) Between [d3] And DMin("dim3","table1","dim3>" & [d3])));

[June7]

And what if they still have the same volume?

And consider - would either of the boxes for PROD10 work? Even with box K dim3 was equal to shipdim3. Will item fit when any dimension is equal to corresponding box dimension?

And look at PROD30. No box will match.