Solved Query v Function

[John Sh]

I have a series of functions that return sql strings as variable rowsources depending on requirements.
For the most part these functions perform as expected except for one that refuses to work, "getCollSearch". see the code below.
When replaced by a query with the same sql the control performs properly, i.e. returns a dropdown list.
The only change made is the query /function selection in "setSources". No other code is altered.

How it works;
When the form opens it is populated with data starting with Genus and working from left to right as selections are made.
In essence the form is filtered by Genus and further filtered by each selection across the form.
In this format the "Collector" control's rowsource is controlled by function "getMColl", below, and it performs normally.
Clicking on the big blue button resets the form to start at either the "location" or "collector" controls and their rowsources are altered to accommodate this.
These changes are made by the "setSources" sub.
Now the form is initially filtered by Collector and/or Location and can be further filtered by the other controls

At form.load or a return to normal, left to right, operation the rowsources are set with "str = F".

It should be noted that my description makes sense to me. If it doesn't make sense to you, I'll try to explain it better. Just let me know what you don't get.

The code below is annotated.
Any variables associated with the functions have been removed for simplicity but the code below is what I have been testing.

'Sets the rowsources as required

Private Sub setSources(str As String)
    If str = "F" Then     
        Me.RecordSource = sTable
        Me.Multi_Search_subform.SourceObject = "query.MSearch"
        Me.cboFamily.RowSource = getMFamily()
        Me.cboGenus.RowSource = getMGenus()
        Me.cboEpithet.RowSource = getMSpecies()
        Me.cboCollector.RowSource = getMColl()
        Me.cboLocality.RowSource = getMLocal()
    Else
        Me.cboCollector.RowSource = "QCollSearch"   'getCollSearch()     'If I use the query "QCollSearch" works. If I use the function "getCollSearch" doesn't
        Me.cboLocality.RowSource = getLocalSearch()
        Me.Multi_Search_subform.SourceObject = "query.maincollectorsearch"
    End If
End Sub


'This is the code on the large blue button

Private Sub btnSearch_Click()
    isLocal = True
    setSources "L"
    Me.cboFamily = ""
End Sub

'This is the cboCollector rowsource on load

Public Function getMColl() As String
    sQry = "SELECT Herbarium_Collection.Collector " & _
            "FROM Herbarium_Collection " & _
            "GROUP BY Herbarium_Collection.Collector, Herbarium_Collection.Genus, Herbarium_Collection.SpeciesEpithet " & _
            "HAVING Herbarium_Collection.Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
            "AND Herbarium_Collection.SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
            "ORDER BY Herbarium_Collection.Collector, Herbarium_Collection.Genus;"
    getMColl = sQry
End Function

'This is the cboCollector rowsource after btnSearch_Click.
'This function produces no results.

Public Function getCollSearch() As String
    sQry = "SELECT herbarium_collection.Collector " & _
            "FROM Herbarium_Collection " & _
            "GROUP BY herbarium_collection.Collector " & _
            "ORDER BY herbarium_collection.Collector;"
    getCollSearch = sQry
End Function

'This is the SQL of query "QCollSearch" taken directly from function "getCollSearch" with obvious removals.
'This works

SELECT Herbarium_Collection.Collector
FROM Herbarium_Collection
GROUP BY Herbarium_Collection.Collector
HAVING (((Herbarium_Collection.Collector)>""))
ORDER BY Herbarium_Collection.Collector;

 

[cheekybuddha]

Are you using aggregate queries (GROUP BY) just to avoid duplicates?

Why not simply use SELECT DISTINCT?

Neither the query in getMColl() nor QCollSearch (also getCollSearch()) need a HAVING clause since you are not filtering dependent on any aggregation - just use a WHERE clause (note it would come before GROUP BY).

Why are you using ANSI92 SQL syntax? Is there any benefit?

Any variables associated with the functions have been removed for simplicity

This is unhelpful - it is easier to offer assistance if we can see the whole code.

What is str ? Is it a variable? Is it a control? [Just saw it's a parameter]

From what you have shown, there is no reason why using getCollSearch() shouldn't work, unless there is a problem with str = "F" and you are not noticing that cboLocality and Multi_Search_subform.SourceObject are also not being set correctly.

Perhaps try first simplifying your queries, eg:

Public Function getCollSearch() As String
    sQry = "SELECT DISTINCT Collector " & _
            "FROM Herbarium_Collection " & _
            "ORDER BY Collector;"
    getCollSearch = sQry
End Function

Public Function getMColl() As String
    sQry = "SELECT DISTINCT Collector " & _
            "FROM Herbarium_Collection " & _
            "WHERE Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
            "AND SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
            "ORDER BY Collector, Genus;"
    getMColl = sQry
End Function

 

[cheekybuddha]

Perhaps also you need to reset the .Value of Me.cboCollector when you reset its RowSource - if it still has a value of something that was in the previous list but not in the new list then it will appear blank (though the list should still contain values to pick from)

 

[John Sh]

<Why are you using ANSI92 SQL syntax? Is there any benefit?>
What is that and what is the alternative? My knowledge of SQL is very limited.

Thank you for your reply David.

As for your suggestion, nothing worked.
In the "getMColl" function, which was working, using "DISTINCT" and "WHERE" together or alone caused the resultant SQL to not work.
I basically copied your code and played with that and then modified my code as suggested with the same result.
I returned to my original code and it worked again. I did this all in the "getMColl" function as it was working in it's original form.
I did try your suggestions in "getCollSearch", to no avail.
Below is the code with variables.

NB.
I tried your code suggestions with and without the variables and all failed to produce a dropdown.

Public Function getMLocal(sTable As String) As String
      sQry = "SELECT A.Locality " & _
             "FROM " & sTable & " as A " & _
             "GROUP BY A.Locality, A.Collector, A.Genus, A.SpeciesEpithet " & _
             "HAVING A.Collector ALike [Forms]![Multi-search]![cboCollector] & '%' " & _
             "AND A.Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
             "AND A.SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
             "ORDER BY A.Locality, A.Genus, A.SpeciesEpithet;"
    getMLocal = sQry
End Function

Public Function getCollSearch(sTable As String) As String
    sQry = "SELECT A.Collector " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.Collector " & _
            "ORDER BY A.Collector;"
    getCollSearch = sQry
End Function

 

[John Sh]

Perhaps also you need to reset the .Value of Me.cboCollector when you reset its RowSource - if it still has a value of something that was in the previous list but not in the new list then it will appear blank (though the list should still contain values to pick from)

When the blue button is pressed, the "Family" value is set to "" which, in turn, sets all the other controls to ""

 

[The_Doc_Man]

Since there is no Aggregate function in the SELECT <field-list> sub-clause, GROUP BY doesn't guarantee distinct or unique records. Look at the indicated clauses in this SELECT statement.

     sQry = "SELECT A.Locality " & _
             "FROM " & sTable & " as A " & _
             "GROUP BY A.Locality, A.Collector, A.Genus, A.SpeciesEpithet " & _               <===
             "HAVING A.Collector ALike [Forms]![Multi-search]![cboCollector] & '%' " & _
             "AND A.Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
             "AND A.SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
             "ORDER BY A.Locality, A.Genus, A.SpeciesEpithet;"                                <===

Did you intentionally omit "ORDER BY A.Collector"? The more I look at the syntax of this statement, the more I'm of the opinion that you can omit certain elements. Since you have no actual SQL Aggregation under way, you should get nearly identical results by dropping the entire GROUP clause, change the HAVING to WHERE, and (at worst) adding A.Collector into the ORDER BY field list. If you have ANY matches at all to A.Collector from your "A.Collector ALike..." clause, is that EVER more than one collector anyway?

There IS a practical side to my suggestion. Each of the SQL elements you used has a separate operational execution phase in standard SQL.

https://www.shiksha.com/online-courses/articles/order-of-execution-in-sql/

You have a single-table FROM clause and no JOIN clause so this is as plain vanilla as it ever gets. In essence, with GROUP and ORDER BY in the same non-aggregated sequence, you are doing the same thing (a sort operation) twice in the same SQL statement, which is a waste of resources. I don't know how fast this is when it runs, but trimming the SQL in the way that has been suggested by me and by others would help your query efficiency and speed. More specifically, the GROUP BY clause forces a sort that would be followed by the HAVING clause. So at that point, the GROUP BY has to sort the entire result-set before then filtering with the HAVING clause. But if you remove the GROUP BY and make the HAVING into a WHERE, when you get around to the ORDER BY you are sorting a (smaller) filtered set of records.

 

[John Sh]

Since there is no Aggregate function in the SELECT <field-list> sub-clause, GROUP BY doesn't guarantee distinct or unique records. Look at the indicated clauses in this SELECT statement.

First, an apology. I posted the wrong function code.

By aggregate in the SELECT statement I assume you mean DISTINCT. I've tried this and there is no return from the functions.

The first of the two images below show the result of modifying the code as you suggested.
The second is the result of my original code.

<is that EVER more than one collector anyway?>
There are about 130 collectors in the table with up to 4,500 collections for one collector. There are 3,800 different genii and some 12,500 species so grouping the data is essential.

My code.
Public Function getMColl(sTable As String) As String
    sQry = "SELECT A.Collector " & _
            "FROM " & sTable & " as A " & _
            "GROUP BY A.Collector, A.Genus, A.SpeciesEpithet " & _
            "HAVING A.Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
            "AND A.SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
            "ORDER BY A.Collector, A.Genus;"
    getMColl = sQry
End Function

Modified as suggested.
Public Function getMColl(sTable As String) As String
    sQry = "SELECT A.Collector " & _
            "FROM " & sTable & " as A " & _
            "WHERE A.Genus ALike [forms]![Multi-search]![cboGenus] & '%' " & _
            "AND A.SpeciesEpithet ALike [forms]![Multi-search]![cboEpithet] & '%' " & _
            "ORDER BY A.Collector, A.Genus, A.SpeciesEpithet;"
    getMColl = sQry'
End Function

With 130+ collectors, this is not ideal even though the collectors shown are restricted to those that have collected Calitryx longiflora in this instance.

This is what I need. The same list of collectors but each only shown once.

While this is all learning for me, it doesn't address my question, why does the query "QCollSearch" work and the function "getCollSearch" not?
Thank you for responding.
John

 

[John Sh]

My original code was missing the "HAVING" clause and this was causing it not to run but was also creating no error message.

My original code.

Public Function getCollSearch() As String
    sQry = "SELECT herbarium_collection.Collector " & _
            "FROM Herbarium_Collection " & _
            "GROUP BY herbarium_collection.Collector " & _
            "ORDER BY herbarium_collection.Collector;"
    getCollSearch = sQry
End Function

Final code that works.

Public Function getCollSearch(sTable As String) As String
    sqry = "SELECT DISTINCT Collector " & _
            "FROM " & sTable & " as A " & _
            "WHERE collector > '';"
    getCollSearch = sqry
End Function

My thanks to all for your input and perseverance.
John