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
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
ASKER
There is no time component. But I could add it. I need the highest serial number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your suggestions.
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