finding a value in a table

[ashoulds]

I need a function that would be able to tell the difference between the size,
color and material and spit out the order code for the option that fits. Any
ideas? An example of my table is below

Size Color Material Order Code
2 B Rubber 1
2 R Rubber 2
2 B Plastic 3
2 R Plastic 4
4 B Rubber 5
4 R Rubber 6
4 B Plastic 7
4 R Plastic 8

 

[Ken Sheridan]

Function GetOrderCode(intSize As Integer, _
strColor As String, _
strMaterial As String) As Long

Dim strCriteria As String
Dim varOrderCode As Variant

GetOrderCode = 0

strCriteria = "Size = " & intSize & _
" And Color = """ & strColor & """" & _
" And Material = """ & strMaterial & """"

varOrderCode = DLookup("[Order Code]", "[YourTable]", strCriteria)

If Not IsNull(varOrderCode) Then
GetOrderCode = varOrderCode
End If

End Function

The function will return the order code if found, zero if not.

I've made assumptions about the data types of the four columns, so you'll
need to amend the code if I've got any wrong.

Ken Sheridan
Stafford, England