Solved Variable in HAVING clause

[John Sh]

I know this is an old beast but none of my reading, or various attempts, has set me on the right path.
In the code below sTable is passed in from the calling sub but is not recognised by the SQL statement.
Replacing the variable with a string, "Collection" in this case causes the SQL to run.
I have tried replacing all, or part, of the HAVING clause with a WHERE clause, and moving it under the FROM clause, with the same result.
How do I fix this?

I could write two SQL strings but this seems a bit much for the sake of one word.
The variable would contain either "Collection" or "Parks".

This code does not work with the variable in the HAVING clause.

Public Function setCounted(sTable As String)
    DoCmd.SetWarnings False
        DoCmd.RunSQL "delete * from Counted"
        sQry = "INSERT INTO counted ( Family, Infra, Box, Collection ) " & _
               "SELECT Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "FROM Boxes " & _
               "GROUP BY Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "HAVING (((Boxes.boxno)>0) and ((instr([Boxes].[Collection], sTable))> '0'));"
        DoCmd.RunSQL sQry
    DoCmd.SetWarnings True
End Function

This code does work with the variable replaced with a string.

Public Function setCounted(sTable As String)
    DoCmd.SetWarnings False
        DoCmd.RunSQL "delete * from Counted"
        sQry = "INSERT INTO counted ( Family, Infra, Box, Collection ) " & _
               "SELECT Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "FROM Boxes " & _
               "GROUP BY Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "HAVING (((Boxes.boxno)>0) and ((instr([Boxes].[Collection], 'Collection'))> '0'));"
        DoCmd.RunSQL sQry
    DoCmd.SetWarnings True
End Function

 

[John Sh]

I enclosed the sTable in " & sTable & ". Now it works.

 

[The_Doc_Man]

I would have thought that since you were building a string anyway, you might use concatenation to pull in the form's value.

"HAVING (((Boxes.boxno) > 0) and ((instr(" & NZ(TRIM([Boxes].[Collection]), "" ) & ", 'Collection')) > 0));"

NOTE: That last "> 0" in YOUR version was " > '0'" - which would never have done what you wanted because INSTR function would return an integer, but you have a string of '0'. The comparison would be off considerably.

 

[John Sh]

I would have thought that since you were building a string anyway, you might use concatenation to pull in the form's value.

"HAVING (((Boxes.boxno) > 0) and ((instr(" & NZ(TRIM([Boxes].[Collection]), "" ) & ", 'Collection')) > 0));"

NOTE: That last "> 0" in YOUR version was " > '0'" - which would never have done what you wanted because INSTR function would return an integer, but you have a string of '0'. The comparison would be off considerably.

Hi. I'm not building a string, I'm restricting the result to a particular part of a field. In this case "Collection", in another case "Parks" hence the variable.
Regarding the '0', I agree this should not work, but it does, or appears to. If I remove the quotations the sql string errors..
I actually finished up using a tempvar.value to get the required result.
I also removed the instr.
My final, working code is this;

Public Function setCounted()
    DoCmd.SetWarnings False
        DoCmd.RunSQL "delete * from Counted"
        sQry = "INSERT INTO counted ( Family, Infra, Box, Collection ) " & _
               "SELECT Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "FROM Boxes " & _
               "GROUP BY Boxes.Family, Boxes.Infra, Boxes.boxno, Boxes.Collection " & _
               "HAVING (((Boxes.boxno)>0) and ((Boxes.Collection) = tempvars!table.value));"
        DoCmd.RunSQL sQry
    DoCmd.SetWarnings True
End Function

I converted this to a function for the immediate window but will revert to a sub now that it works.

As always, I appreciate your input.
John

 

[Mark_]

Please note, you can use debug.print or a MsgBox to show you the contents of your sQry before you run it.
I'd ALWAYS use something like this to verify my SQL is correct before ever trying to use it on data.

 

[June7]

You are building a string - a string that is an SQL statement. I think that's what Doc was getting at. Need to concatenate VBA variables, you had sTable embedded. Embedded TempVars will work because Access SQL can evaluate TempVars.

 

[The_Doc_Man]

I'm not building a string,

Just as a fine point, you WERE building a string. Starting with ...

        sQry = "INSERT INTO counted ( Family, Infra, Box, Collection ) " & _

However, since you have it working, I'm easily among the first to admit that there is more than one way to skin a cat.

 

[John Sh]

Just as a fine point, you WERE building a string. Starting with ...

        sQry = "INSERT INTO counted ( Family, Infra, Box, Collection ) " & _

However, since you have it working, I'm easily among the first to admit that there is more than one way to skin a cat.

Point taken. I wasn't considering the sql string as a whole, but just that particular part of it.

 

[John Sh]

Please note, you can use debug.print or a MsgBox to show you the contents of your sQry before you run it.
I'd ALWAYS use something like this to verify my SQL is correct before ever trying to use it on data.

I've never really got into debug.print but I do frequently use msgbox as a debugging tool. In the case of an sql string, however, the cause of the error is often a bit vague. In this particular case the error messages were just a little above useless.
As for running on data, I never run untested code on live data, always on a separate copy of the data where the end result causes no loss.