RowSource for Combobox using max function

[simaonobrega]

Dear bytes community,

FrameWork:
I have a table with the following fields: Id, Name, InterventionNum ber, CheckPoint, Completed
In a form, I have a combobox that I want to populate with the entries that are obtained using the following criteria:
-First: check the maximum InterventionNum ber for each unique Name;
-Second: filter the results of the first operation using the Completed field (it is a checkbox) equals True

Code Used:

Dim qry as String
qry = "Select Id, Name, Max(InterventionNumber) From [table]" & "Where Completed = True " & "Group by Id, Name"
Combobox.RowSource = qry

Problem:
Max function does not work because Id is always different (autonumber) despite the field "Name" possible repetitions.

Question:
How can I apply the filter just taking into account the Name and InterventionNum bers fields but being able to put all the fields that I want in the Combobox.

Thank you very much for your help.

Best regards,
Simão

[codegazer]

If I understand this correctly, you are trying to create a list of latest completed interventions for each name.

Instead of trying to use the MAX function with associated auto ID problems, try this approach.

1. Read each line in turn, complete with row number.
2. If the check is not completed, discard the line.
3. If the check is True
3a Use the 'name' field to compare with any existing entry.
3b If the name is not listed, add the line
3c If the name already exists, compare intervention number
3d If the number is greater than the existing one, replace it.

Your combo will be then filled with the latest intervention numbers for each name.