Format a RunSQL Statement in VBA
Thanks for the response. I will do it that way. As usual - a simple
solution :-)
Actually the SQL returns a single value - the median - and as regards the
math - it's the median I'm after. The SQL is fifth in a line of queries to
get the median. The first four are make table and append queries - which
run fine in VBA - the origin of my mistake.
Thanks again.
John
Just as an FYI
The SQL Statements:
1)
CREATE TABLE Working
(UnitPrice REAL NOT NULL,
occurs INTEGER NOT NULL);
2)
INSERT INTO Working ( UnitPrice, occurs )
SELECT [OrderDetails].[UnitPrice], COUNT(*) AS Expr1
FROM OrderDetails
GROUP BY [OrderDetails].[UnitPrice];
3)
CREATE TABLE Summary
(UnitPrice REAL NOT NULL,
occurs INTEGER NOT NULL,
pretally INTEGER NOT NULL,
posttally INTEGER NOT NULL);
4)
INSERT INTO Summary
SELECT S2.UnitPrice AS UnitPrice, S2.occurs AS occurs,
SUM(S1.occurs)-S2.occurs AS pretally, SUM(S1.occurs) AS posttally
FROM Working AS S1, Working AS S2
WHERE S1.UnitPrice<=S2.UnitPrice
GROUP BY S2.UnitPrice, S2.occurs;
5)
SELECT AVG(S3.UnitPrice) AS median
FROM summary AS S3
WHERE (S3.posttally > (SELECT MAX(posttally) / 2.0 FROM summary)
AND S3.pretally < (SELECT MAX(posttally) / 2.0 FROM summary))
OR S3.pretally = (SELECT MAX(posttally) / 2.0 FROM summary)
OR S3.posttally = (SELECT MAX(posttally) / 2.0 FROM summary);
Quote:
> RunSQL can only be used with Action or Data-Definition Queries and NOT
> *Select* Queries which you are trying to do.
> Try creating Recordset from the Query. Since your Query only returns only
1
> Filed Value, I think it is more efficient to use the Access function
DAvg()
> rather than the Query and Recordset.
> Check Access Help on RunSQL and DAvg().
> BTW, Average is NOT Median in general. Are you sure you got your Maths /
> mathematical terms correct?
> HTH
> Van T. Dinh
> > To the group.
> > I have tried to research this but can not find any particular "how to's"
> so
> > I'll ask you.
> > The following SQL statement runs in a query:
> > SELECT AVG(S3.UnitPrice) AS median
> > FROM summary AS S3
> > WHERE (S3.posttally > (SELECT MAX(posttally) / 2.0 FROM summary)
> > AND S3.pretally < (SELECT MAX(posttally) / 2.0 FROM summary))
> > OR S3.pretally = (SELECT MAX(posttally) / 2.0 FROM summary)
> > OR S3.posttally = (SELECT MAX(posttally) / 2.0 FROM summary);
> > I have attempted to use the
> > DoCmd.RunSQL "Then the statement above - and all on one line for
> simplicity
> > (or so I thought)...
> > But I get
> > Run Time error '2342'
> > A RunSQL action requires an argument consisting of an SQL statement.
> > I'm a little confused. The statement runs in the query fine. What am I
> > missing here?
> > Any help is appreciated.
> > Thanks
> > And thanks for help in the past.
> > John Kaurloto
