Solved

vba query to find record based on two criteria

Posted on 2011-09-02
6
475 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 40

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

861 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