 JustJim  407
Recognized Expert Contributor
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
5 2414  JustJim 407
Recognized Expert Contributor
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 407
Recognized Expert Contributor
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 407
Recognized Expert Contributor
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
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 407
Recognized Expert Contributor
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!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Newbie |
last post by:
Dear friends,
I am having a hard time understanding how to use a SELECT CASE in ASP. I
have used it in VB but never in ASP scripting.
Scenerio:
I have 2 textboxes on a form that I have to allow entry to one or the other
or Both at the same time. Now I tried to use an If ElseIf but it got too
hard to track, so now I am using a SELECT CASE Statement.
|
by: Ian T |
last post by:
Hi,
I've got what I think (probably incorrectly) should be a simple SELECT :
Two colums with data like
col1 col2
1 50
1 51
2 50
|
by: malcolm |
last post by:
Example, suppose you have these 2 tables
(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)
CarsSold {
CarsSoldID int (primary key)
MonthID int
DealershipID int
NumberCarsSold int
|
by: Adam Balgach |
last post by:
I am having a bit of a problem, and it might have to do with my
understanding of how select works...
basically what i am trying to simulate is a system:
client->gateway->server model. The client breaks a file up into
sections and sends them to the server, through teh gateway which just
forwards info. Now upon getting the info, the server sends back an
ACK through the gateway, when the client sees the ack it sends the
next part...
|
by: jimh |
last post by:
I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.
I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:
(User Table)
UserID
Other user data
| | |
by: GreatAlterEgo |
last post by:
Hi,
This is my query which is embedded in a COBOL program.
EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
|
by: pekx |
last post by:
Hi,
I create a script to pick up those records with duplication of IMIPROPSERVNO field, see below test1.sql, which is working....
test1.sql
1.SELECT IMIPROPSERVNO, COUNT(*)
2.FROM INSTALL_MASTER#IMIPROPSERN
3: GROUP BY IMIPROPSERVNO
4: HAVING COUNT(*) > 1;
Now, I want a list of records in INSTALL_MASTER#IMIPROPSERN where the count > 1, ie those records with duplicated IMIPROPSERVNO.
I tried the following but did not...
|
by: The.Daryl.Lu |
last post by:
Hi, two parts to my problem if someone can help address either one or
both:
1. I want to SELECT everything in the table if it matches the
criteria when the query button is pressed (this is just a
commandbutton on my form). The biggest problem I'm having is that I
can't (don't know how) populate a combobox on static items such as
"True, False, Any." The Any field, would be selected by the user to
find all records where that fields is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
