Solved

vba query to find record based on two criteria

Posted on 2011-09-02
6
473 Views
Last Modified: 2012-05-12
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
Comment
Question by:gbnorton
6 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 36475971
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
 

Author Comment

by:gbnorton
ID: 36476009
There is no time component.  But I could add it.  I need the highest serial number.
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 167 total points
ID: 36476111
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 167 total points
ID: 36476941
@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
 
LVL 39

Assisted Solution

by:als315
als315 earned 166 total points
ID: 36478081
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
 

Author Closing Comment

by:gbnorton
ID: 36502443
Thanks for all your suggestions.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now