Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

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
0
gbnorton
Asked:
gbnorton
3 Solutions
 
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
 
pteranodon72Commented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
aikimarkCommented:
@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
 
als315Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now