Solved

vba query to find record based on two criteria

Posted on 2011-09-02
6
472 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

8 Experts available now in Live!

Get 1:1 Help Now