422,767 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,767 IT Pros & Developers. It's quick & easy.

RowSource for Combobox using max function

P: 22
Dear bytes community,

FrameWork:
I have a table with the following fields: Id, Name, InterventionNumber, 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 InterventionNumber for each unique Name;
-Second: filter the results of the first operation using the Completed field (it is a checkbox) equals True

Code Used:
Expand|Select|Wrap|Line Numbers
  1. Dim qry as String
  2. qry = "Select Id, Name, Max(InterventionNumber) From [table]" & "Where Completed = True " & "Group by Id, Name"
  3. 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 InterventionNumbers 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
Sep 6 '17 #1

✓ answered by NeoPa

Hi Simão.

There seems to be some confusion. The code you've posted cannot possibly work as it stands, as qry1 is a String value and would need to be a QueryDef in order for it to be used, as it appears to be, in qry2. Maybe you also saved qry1 as a QueryDef.

Actually, it's not even necessary to have them as two separate QueryDefs or Strings. Try this :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select [tV].[ValveName]" _
  2.        & "     , [tV].[Factory]" _
  3.        & "     , [tV].[InterventionNumber]" _
  4.        & "     , [tV].[Completed]" _
  5.        & "     , [tV].[ID]" _
  6.        & "FROM   [tbValve] AS [tV]" _
  7.        & "       INNER JOIN" _
  8.        & "       (SELECT   [Factory]" _
  9.        & "               , [ValveName]" _
  10.        & "               , Max([InterventionNumber]) AS [MaxOfInterventionNumber]" _
  11.        & "        FROM     [tbValve]" _
  12.        & "        GROUP BY [Factory]" _
  13.        & "               , [ValveName]) AS [q1]" _
  14.        & "  ON   ([tV].[Factory]=[q1].[Factory])" _
  15.        & " AND   ([tV].[ValveName]=[q1].[ValveName])" _
  16.        & " AND   ([tV].[InterventionNumber]=[q1].[MaxOfInterventionNumber])" _
  17.        & "WHERE  ([tV].[Completed])"
  18. cbValve.RowSource = strSQL

Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,117
Air Code
Expand|Select|Wrap|Line Numbers
  1. Qry= "SELECT MyTable.* FROM MyTable WHERE InterventionNumber =  DMax("InterventionNumber", "MyTable", "[Name] = " & Chr$(34) & [Name] & Chr$(34))
  2.  
So the idea is to find the maximum InterventionNumber for each name, then find all the records that match that InterventionNumber

Phil
Sep 6 '17 #2

P: 22
Hello PhilOfWalton,

Thank you for your reply.
I have understood the ideia but not the code, in particular the last part where it is used Chr$(34).

Best regards
Sep 6 '17 #3

NeoPa
Expert Mod 15k+
P: 30,744
Hi Simão.

Can you explain which fields you need in the ComboBox. Also, your explanation left the situation a little unclear as to what was required when the maximum [InterventionNumber] found is for a record where [Completed] <> True. Does that mean the name is excluded from the result set? Or does it mean that you simply discount that one record and select the next greatest value of [InterventionNumber] where [Completed] = True?

What you're looking for would require a subquery of some form. Exactly where and how depends on your answers. I don't recommend the use of Domain Aggregate functions in such a case as they are an inefficient use of the tools available to you. They can get the job done, but should be avoided where possible.

The benefits of subqueries over Domain Aggregate functions is that the database engine (Generally ACE nowadays.) has all the information available to it so can optimise intelligently, and this is certainly a situation where optimising can save a chunk of work.
Sep 6 '17 #4

NeoPa
Expert Mod 15k+
P: 30,744
Simão:
I have understood the idea but not the code, in particular the last part where it is used Chr$(34).
Chr$(34) is simply another way of saying the double-quote character ("). When using strings in VBA the character itself has a special meaning so using it as itself would not have the desired effect. Alternatives to this are to double them up, so "([X]=""Blob"")" would work, or to use the correct quote character for SQL strings, so "([X]='Blob')" would also work and be more standard.

For more details on this whole area see Quotes (') and Double-Quotes (") - Where and When to use them.
Sep 6 '17 #5

P: 22
Hello NeoPa,

Thank you very much for all the information given.
In reality, I have a table with the following fields: Id, ValveName, InterventionNumber, Factory, Completed.

In the combobox I want to put ValveName, Factory and InterventionNumber.
First the results are filtered by maximum InterventionNumber for each unique combination of ValveName and Factory. After this step, a filter is applied again (completed = True).

It was given this solutions to me:
Expand|Select|Wrap|Line Numbers
  1. qry1 = "Select ValveName, Factory, Max(InterventionNumber) AS MaxOfInterventionNumber FROM tbValve GROUP BY ValveName, Factory"
  2. qry2 = "Select tbValve.ValveName, tbValve.Factory, tbValve.InterventionNumber, tbValve.Completed, tbValve.ID FROM tbValve INNER JOIN qry1 ON (tbValve.InterventionNumber = qry1.MaxOfInterventionNumber) AND (tbValve.Factory = qry1.Factory) AND (tbValve.ValveName = qry1.ValveName) WHERE (((tbValve.Completed)=True));"
  3. cbValve.RowSource = qry2
I don't know if it is the more efficient one but it is working. It is a solution obtained via the query design grid to do the joins and then copying the VBA code to the VBA environment.
Sep 7 '17 #6

PhilOfWalton
Expert 100+
P: 1,117
@simaonobrega

Chr$(34) is double quotes (")

You can possibly use
Expand|Select|Wrap|Line Numbers
  1. Qry= "SELECT MyTable.* FROM MyTable WHERE InterventionNumber =  DMax("InterventionNumber", "MyTable", "[Name] = '"  & [Name] & "'"
  2.  
I tend to use this, as things like "'" can be very confusing, depending on the font being used.

Phil
Sep 7 '17 #7

NeoPa
Expert Mod 15k+
P: 30,744
Hi Simão.

There seems to be some confusion. The code you've posted cannot possibly work as it stands, as qry1 is a String value and would need to be a QueryDef in order for it to be used, as it appears to be, in qry2. Maybe you also saved qry1 as a QueryDef.

Actually, it's not even necessary to have them as two separate QueryDefs or Strings. Try this :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select [tV].[ValveName]" _
  2.        & "     , [tV].[Factory]" _
  3.        & "     , [tV].[InterventionNumber]" _
  4.        & "     , [tV].[Completed]" _
  5.        & "     , [tV].[ID]" _
  6.        & "FROM   [tbValve] AS [tV]" _
  7.        & "       INNER JOIN" _
  8.        & "       (SELECT   [Factory]" _
  9.        & "               , [ValveName]" _
  10.        & "               , Max([InterventionNumber]) AS [MaxOfInterventionNumber]" _
  11.        & "        FROM     [tbValve]" _
  12.        & "        GROUP BY [Factory]" _
  13.        & "               , [ValveName]) AS [q1]" _
  14.        & "  ON   ([tV].[Factory]=[q1].[Factory])" _
  15.        & " AND   ([tV].[ValveName]=[q1].[ValveName])" _
  16.        & " AND   ([tV].[InterventionNumber]=[q1].[MaxOfInterventionNumber])" _
  17.        & "WHERE  ([tV].[Completed])"
  18. cbValve.RowSource = strSQL
Sep 7 '17 #8

P: 22
PhilOfWalton,

Thank you for your reply. I will, from now on, use your recommendation. Is indeed less confusing.

NeoPa,

Thank you for your reply. You are absolutely right. My VBA code was working because I had the queries in the database. When I deleted them, the code stopped working (because qry1 was just a string as stated by you).
When I used your code, it started working. Amazing!!! Wasn't able to do it by myself (even with the time I had lost trying to put this working - many hours).
I am very grateful!!

Regards,
Simão
Sep 8 '17 #9

NeoPa
Expert Mod 15k+
P: 30,744
You're very welcome Simão.

Please don't be toooo surprised my suggested code works. You'll ruin my reputation ;-)
Sep 8 '17 #10

P: 22
A well deserved reputation from your knowledge and contribution to the forum :)
Sep 9 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.