424,054 Members | 1,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Select... Into... Having

JustJim
Expert 100+
P: 407
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!

Expand|Select|Wrap|Line Numbers
  1. '   Canberra Site Report export to Excel
  2.     If Sbf.SourceObject = "sfmSiteOnlyCanberra" Then
  3.             strExportTableName = "tblXLCanberraSites"
  4.         '   Gather the selected Projects
  5.         For Each varSelect In Sbf.Controls("lstSite").ItemsSelected
  6.             strOr = strOr & "(tblSite.SiteNo) = '" & Sbf.Controls("lstSite").ItemData(varSelect) & "' Or "
  7.         Next varSelect
  8.     '   lose the last ' OR '
  9.     strOr = Left(strOr, Len(strOr) - 4)
  10.     If strOr <> "" Then
  11.         strOr = "(" & strOr & ")"
  12.     End If  '   strOr <> ""
  13.  
  14.     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 " _
  15.         & "INTO " & strExportTableName & " " _
  16.         & "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 " _
  17.         & "WHERE (((tblSite.SiteActive)=True)) " _
  18.         & "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 "
  19.     If strOr <> "" Then
  20.         strSQL = strSQL & " HAVING " & strOr
  21.     End If  '   strOr <> ""
  22.  
  23.     'Make note of any fields that have to have their data type fixed
  24.     ReDim arFixField(1, 0)
  25.     arFixField(0, 0) = "SamNo"
  26.     arFixField(1, 0) = "INTEGER"
  27.     iArrayCounter = 0
  28.     DoCmd.SetWarnings False
  29.     If Application.CurrentData.AllTables(strExportTableName).IsLoaded Then
  30.         DoCmd.Close acTable, strExportTableName, acSaveNo
  31.     End If  '   Application.CurrentData.AllTables(strExportTableName).IsLoaded
  32.     DoCmd.RunSQL strSQL
  33.     For iLoop = 0 To iArrayCounter  '   fix up any column datatypes as noted earlier
  34.         strExecuteCommand = "Alter Table " & strExportTableName & " ALTER COLUMN " & arFixField(0, iLoop) & " " & arFixField(1, iLoop)
  35.         DoCmd.RunSQL strExecuteCommand
  36.     Next iLoop
  37.  
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
Oct 25 '07 #1
Share this Question
Share on Google+
5 Replies


JustJim
Expert 100+
P: 407
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
Oct 25 '07 #2

JustJim
Expert 100+
P: 407
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
Oct 25 '07 #3

JustJim
Expert 100+
P: 407
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
Oct 25 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 26 '07 #5

JustJim
Expert 100+
P: 407
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!
Oct 26 '07 #6

Post your reply

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