Select... Into... Having

[JustJim]

I think I've just bumped into the limits of the Having Clause (Help file says 40 expressions, Mary says 99 and I know who I trust).

The question is, what happens if you overload the select... into... having clause? Does the whole thing fail? Is the clause truncated somehow?

It appears that the target table is being deleted, but then not re-created!

Here's what I got so far. Please pardon the long lines, I don't type 'em, I just paste from SQL view of the query designer!

'   Canberra Site Report export to Excel
    If Sbf.SourceObject = "sfmSiteOnlyCanberra" Then
            strExportTableName = "tblXLCanberraSites"
        '   Gather the selected Projects
        For Each varSelect In Sbf.Controls("lstSite").ItemsSelected
            strOr = strOr & "(tblSite.SiteNo) = '" & Sbf.Controls("lstSite").ItemData(varSelect) & "' Or "
        Next varSelect
    '   lose the last ' OR '
    strOr = Left(strOr, Len(strOr) - 4)
    If strOr <> "" Then
        strOr = "(" & strOr & ")"
    End If  '   strOr <> ""
 
    strSQL = "SELECT tblSite.SiteNo, tblSite.SiteDescription, tblSite.NatMapRefN, tblSite.NatMapRefE, tblSite.MapNo, tblSite.GISZoneNo, qryAWName.NamCatch, qryAWName.NamSub, tbzElectorate.Electorate, tbzWaterType.WaterTypeDesc, tbzParam.DescShort, tblSample.SamNo, tblSample.Date " _
        & "INTO " & strExportTableName & " " _
        & "FROM tbzWaterType RIGHT JOIN (tbzParam RIGHT JOIN (((tblSite INNER JOIN qryAWName ON tblSite.AWCode = qryAWName.AWCode) INNER JOIN tbzElectorate ON tblSite.ElectID = tbzElectorate.ElectID) LEFT JOIN ((tblMonSite LEFT JOIN tblSample ON tblMonSite.GSPNo = tblSample.GSPNo) LEFT JOIN tblResult ON tblSample.SamNo = tblResult.SamNo) ON tblSite.SiteNo = tblMonSite.SiteNo) ON tbzParam.ParamNo = tblResult.ParamNo) ON tbzWaterType.WaterType = tblSite.WaterType " _
        & "WHERE (((tblSite.SiteActive)=True)) " _
        & "GROUP BY tblSite.SiteNo, tblSite.SiteDescription, tblSite.NatMapRefN, tblSite.NatMapRefE, tblSite.MapNo, tblSite.GISZoneNo, qryAWName.NamCatch, qryAWName.NamSub, tbzElectorate.Electorate, tbzWaterType.WaterTypeDesc, tbzParam.DescShort, tblSample.SamNo, tblSample.Date "
    If strOr <> "" Then
        strSQL = strSQL & " HAVING " & strOr
    End If  '   strOr <> ""
 
    'Make note of any fields that have to have their data type fixed
    ReDim arFixField(1, 0)
    arFixField(0, 0) = "SamNo"
    arFixField(1, 0) = "INTEGER"
    iArrayCounter = 0
    DoCmd.SetWarnings False
    If Application.CurrentData.AllTables(strExportTableName).IsLoaded Then
        DoCmd.Close acTable, strExportTableName, acSaveNo
    End If  '   Application.CurrentData.AllTables(strExportTableName).IsLoaded
    DoCmd.RunSQL strSQL
    For iLoop = 0 To iArrayCounter  '   fix up any column datatypes as noted earlier
        strExecuteCommand = "Alter Table " & strExportTableName & " ALTER COLUMN " & arFixField(0, iLoop) & " " & arFixField(1, iLoop)
        DoCmd.RunSQL strExecuteCommand
    Next iLoop
 

Depending on the user's back end tables and the choices they make from the multi-select listbox, this can end up with a len(strSQL) of > 11,000!, but that's within the 32,768 limit.

Oddly, when no table is created, the second RunSQL action (to fix up datatypes) doesn't cause an error.

Jim

[JustJim]

Stop Press!

I just noticed that when the operator makes no choice from the listbox, (making strOR = "" and therefore there is no Having clause at all), the same thing happens! The table is deleted but not re-created and the second RunSQL doesn't cause an error. (I rem'd out the SetWarnings False first).

If I paste strSQL into the SQL view of the query designer it works fine with a small Having clause or without one.

Things that make you go "Hmmm"

Jim

[JustJim]

Stop Press!

If I paste strSQL into the SQL view of the query designer it works fine with a small Having clause or without one.

Jim

Actually, that turns out not to be the case. If I paste strSQL into the SQL view of the query designer, I can view the query in Datasheet view, but if I run the query I get

"You tried to assign the Null value to a variable that is not a Variant
data type"

Still hunting!

Jim

[JustJim]

Actually, that turns out not to be the case. If I paste strSQL into the SQL view of the query designer, I can view the query in Datasheet view, but if I run the query I get

"You tried to assign the Null value to a variable that is not a Variant
data type"

Still hunting!

Jim

Got it!

It was the null value that put me onto it. One (and darn it, only one) of the Dates in tblSample were Null and that threw the whole thing out.

Big thanks to me for all my help! (No, not being snippy, I'm actually glad I sorted it myself)

Have a great weekend all.

Jim

[missinglinq]

We just love listening to your accent, mate! You understand, of course, that being "Down Under," everything you do in Access works bass ackwards!

Linq ;0)>

[JustJim]

We just love listening to your accent, mate! You understand, of course, that being "Down Under," everything you do in Access works bass ackwards!

Linq ;0)>

Crikey! That explains a lot.
Jim (.^.) that's me smiling down-side-up!