vba query to find record based on two criteria

My form will prompt the user for Part Number.
In the table the records look like this:
PartNumber     CreateDate     LastSerialNumber
1                        1/1/2011            1
1                        1/1/2011            10
1                        1/1/2011            20

I need the vba code to return the record with the '20'.  In this table there are many different part numbers, dates, and serial numbers.  I need the record with the last serial number used for that part number and most recent date.  

Thanks,
Brooks
gbnortonAsked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
OK -- just so you know in case of ties. Your data may not have ties, but defining a rule helps.

This assumes that LastSerialNumber and PartNumber are both defined as numeric, not text. Write back if either are text.

Dim lngLastSerialNumber as Long
lngLastSerialNumber = DMax("LastSerialNumber", "YourTable", "Partnumber = " & lngPartNumber & " AND CreateDate=(SELECT Max(CreateDate) FROM YourTable WHERE PartNumber = " & lngPartNumber & ")")

HTH,
pT72
0
 
pteranodon72Commented:
gbnorton,

Does the CreateDate field include a time component? In your example, the three records have exactly the same date -- there is no way to distinguish the LastSerial Number by "most recent". Even if the table looks sorted by order of entry or ascending LastSerialNumber, it really isn't stored that way.

If there are ties, what LastSerialNumber would you want to return -- highest?

pT72
0
 
gbnortonAuthor Commented:
There is no time component.  But I could add it.  I need the highest serial number.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
aikimarkConnect With a Mentor Commented:
@Brooks

If you need the greatest LastSerialNumber, then I don't see any reason to consider the date.

Public Function LastSerialNumberForPart(parmPartNum) As Long
   LastSerialNumberForPart = DMax("LastSerialNumber", "MyPartsTable", "Partnumber = " & parmPartNum)
End Function

Open in new window


You can also use the DMax() domain function in a SQL statement.

example:
Select *, 
DMax("LastSerialNumber", "MyPartsTable", "Partnumber = " & SomeTableThatHasAPartNumberColumn.PartNumber) As LastPartNo
From SomeTableThatHasAPartNumberColumn

Open in new window


You can also do this in SQL
Select PartNumber, Max(CreateDate) As MaxPartDate, 
Max(LastSerialNumber) As PartLastSerNum 
From MyPartsTable
Group By PartNumber

Open in new window

0
 
als315Connect With a Mentor Commented:
If you want to find record on current form, old function from MS site can be helpful(modified to current Access vesions):
 
Function FindRecord_RS(SQLWhere)
Dim DS As Recordset
Set DS = Screen.ActiveForm.Recordset.Clone
DS.FindFirst SQLWhere
If DS.NoMatch Then
       MsgBox "No record found!"
       FindRecord_RS = False
Else
       Screen.ActiveForm.Bookmark = DS.Bookmark
       FindRecord_RS = True
End If
End Function

Open in new window

Examples of use you can find in original article:
http://support.microsoft.com/kb/114556
Combine your PartNumber and dmax result.
0
 
gbnortonAuthor Commented:
Thanks for all your suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.