Solved It needs a bracket

[John Sh]

This sql string tells me it expects ")".
There must be one place I haven't tried and I have no idea where that place is.
The problem is in the where clause and specifically after the "And", as I read it.
The where clause was originally a "Having" clause but this seemed to be causing even more trouble.
The function is used as the rowsource for a combobox.

Suggestions please.

Public Function getCommonName(sGenus As String, sSpecies As String) As String
    Dim sQry As String
    sQry = "SELECT A.Common " & _
           "FROM Common_Names as A " & _
           "GROUP BY A.Common, A.Genus, A.Species " & _
           "WHERE (((A.Genus) ='" & sGenus & "'" And ((A.Species) = "'" & sSpecies & "'"" )) " & _
           "ORDER BY A.Common;"
    getCommonName = sQry
End Function

 

[June7]

Parenthesis must be in pairs - that means an even number. You have nine parens - none of them are actually needed in that clause. Access throws them in with query designer but you can delete them in the VBA construct.

GROUP BY clause must be after WHERE clause.

Doesn't make sense to me to GROUP BY fields not in SELECT.

Could use SELECT DISTINCT instead of GROUP BY.

 

[arnelgp]

Suggestions please.

I suggest to remove them, since your query will work without them.
I think you just copy what the SQL designer suggested.

Public Function getCommonName(sGenus As String, sSpecies As String) As String
    Dim sQry As String
    sQry = "SELECT A.Common " & _
           "FROM Common_Names as A " & _
           "WHERE A.Genus ='" & sGenus & "' And A.Species = '" & sSpecies & "' " & _           
           "GROUP BY A.Common, A.Genus, A.Species " & _
           "ORDER BY A.Common;"
    getCommonName = sQry
End Function

 

[CJ_London]

Agree not needed but if you wanted to you need a ) before the And

 

[MajP]

So you want to resolve to something like this which is perfectly fine
WHERE A.Genus = 'SomeGenus' And A.Species = 'SomeSpecies'

You could maybe add parentheses to the individual = parts if that helps it read better
WHERE (A.Genus = 'SomeGenus') And (A.Species = 'SomeSpecies')

But surrounding the whole where is way overkill and does nothing
WHERE ((A.Genus = 'SomeGenus') And (A.Species = 'SomeSpecies'))

And this is just ridiculous
WHERE (((A.Genus) = 'SomeGenus') And ((A.Species) = 'SomeSpecies'))

The designer does overkill and most of the time you can take most if not all of that out. You only need it to force the order of operations. Often where you may need it where you mix And and Or and you need the parentheses because it has a very different meaning depending on order evaluated. And sometimes that is unclear at which order it will happen
WHERE A.Genus = 'SomeGenus' OR A.Species = 'SomeSpecies' AND Selected = true

These return very different results.
WHERE A.Genus = 'SomeGenus' OR (A.Species = 'SomeSpecies' AND Selected = true)
WHERE A.Genus = ('SomeGenus' OR A.Species = 'SomeSpecies') AND Selected = true

 

[The_Doc_Man]

There are times that I wonder if the query's designer function was based on the early version of the programming language LISP... which in my college days was interpreted as "Lots of Insipid Silly Parentheses." The query designer, as MajP points out, seems to like parentheses.

 

[CJ_London]

Designer also uses square brackets even when not required and table names regardless even when they are only required when a field name appears in more than one table. I guess because the formatting routine works at a lower level for these

 

[John Sh]

I suggest to remove them, since your query will work without them.
I think you just copy what the SQL designer suggested.

Public Function getCommonName(sGenus As String, sSpecies As String) As String
    Dim sQry As String
    sQry = "SELECT A.Common " & _
           "FROM Common_Names as A " & _
           "WHERE A.Genus ='" & sGenus & "' And A.Species = '" & sSpecies & "' " & _         
           "GROUP BY A.Common, A.Genus, A.Species " & _
           "ORDER BY A.Common;"
    getCommonName = sQry
End Function

Thank you.
A constructive suggestion is most useful.

 

[John Sh]

My sincere thanks to all.
The brackets have been removed.
I find the error messages, in many cases, do nothing but increase the angst.
Messages like "Expected bracket" etc are of little, or no, value.
I finished up using Arnelgp's suggestion with "Select distinct" added and the group clause removed.
Many thanks to all.
John

 

[GPGeorge]

My sincere thanks to all.
The brackets have been removed.
I find the error messages, in many cases, do nothing but increase the angst.
Messages like "Expected bracket" etc are of little, or no, value.
I finished up using Arnelgp's suggestion with "Select distinct" added and the group clause removed.
Many thanks to all.
John

I find error messages like "Expected bracket" to be quite useful in troubleshooting incorrectly written SQL. Not that I ever do write anything incorrectly, of course.

That one, for example, tells you to look for a missing bracket.

Brackets come in pairs. Opening and closing. If you miss one where it's required, the SQL can't run. Knowing the reason for the failure helps you track down the problem.