473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

RowSource for Combobox using max function

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

10 1823
PhilOfWalton
1,430 Expert 1GB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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
1,430 Expert 1GB
@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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
A well deserved reputation from your knowledge and contribution to the forum :)
Sep 9 '17 #11

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

Similar topics

1
by: Mike | last post by:
In C, we can typedef pointer to functions, and therefore use function tables. But what's the advantage of using function table? Thanks, Mike
0
by: Hiroyuki Tanaka | last post by:
Hi All, I am trying to develop an application for a touch screen using buttons for the numeric pad with Completion ComboBoxes. At the moment I am having a problem sending the button presses to...
2
by: Newbie | last post by:
Could someone help: I have been searching the web trying to figure out a way to use function key's in my vb.net 2003 program (No luck!). What I would like to do is enable the user to press (for...
3
by: drsantosh82 | last post by:
Hi, I am trying to implement a callback routine using function pointers. Basically, I am trying to avoid tying my callback invoking member to a particular class. Let me explain my problem...
6
by: x taol | last post by:
i want to change textbox to combobox type using vba. *** Sent via Developersdex http://www.developersdex.com ***
1
by: gourab103111 | last post by:
how get multiple values from combobox using php ,when i retrive value using $_POST
2
by: nikhizumi | last post by:
advantages of using FUNCTION in C++
1
by: shubham rastogi | last post by:
I am using a combobox.. and I want to display two columns combobox using the diplay member property...for example I have a table sam and it has two columns A and B . following are details of...
1
by: sharifsayyed | last post by:
<xml> <beneficiary> <District name="Nandurbar"> <Taluka>Nandurbar</Taluka> <Taluka>Nawapur</Taluka> <Taluka>Akkalkuva</Taluka> </District> <District name="Dhule"> ...
1
by: simaonobrega | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.