two variable dlookup string

  • Thread starter Thread starter Brenton Anderson
  • Start date Start date
B

Brenton Anderson

how would i structure the string in a do lookup function
to lookup part numbers under a po/order#, whereby part
number is multiple lines?

POnumber, OrderNo, Partnumber
1234 7890 abc
1234 7890 def
1234 7890 ghi
5678 4567 abc
5678 4567 def


brenton
 
how would i structure the string in a do lookup function
to lookup part numbers under a po/order#, whereby part
number is multiple lines?

POnumber, OrderNo, Partnumber
1234 7890 abc
1234 7890 def
1234 7890 ghi
5678 4567 abc
5678 4567 def
Click to expand...

What do you want to look up? All lines for Partnumber abc? DLookUp
looks up one single value - if that's ok, which PONumber do you want?

Please explain the context and what you're expecting to get as a
result.
 
Brenton,

As has been noted you can't look up more than one record with DLookup().

You can write your own routine though. The code below assumes you are using
DAO.

Function GetPartNumber(lngPONumber As Long, lngOrderNo As Long) As String
Dim rs As Recordset
Dim strPartNumber As String
Dim strSQL As String

strSQL = "SELECT PartNumber FROM PartNumberTable WHERE OrderNo=" &
lngOrderNo & " AND PONumber=" & lngPONumber ' this should be a single
line
Set rs = CurrentDb.OpenRecordset(strSQL,dbOpenForwardOnly,dbReadOnly)
Do While Not rs.EOF
strPartNumber = strPartNumber & rs!PartNumber
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
GetPartNumber = strPartNumber
End Function
 
Back
Top