I have a function that sends query outputs to excel...

In essence, the relevant part is

Sub QueryToExcel()
' Send Query Output results to Excel

Dim rstOutput As ADODB.Recordset
Dim objXL As Excel.Application
Dim objWS As Excel.Worksheet
Dim fld As ADODB.Field
Dim intCol As Integer
Dim intRow As Integer

Set rstOutput = New ADODB.Recordset

' This is where I keep getting an error
'Run-time error '-2147217900 (80040e14):
'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
''SELECT', or 'UPDATE'.

rstOutput.Open "QueryOutput", CurrentProject.Connection

the rest of the function lauches Excel, creates a worksheet, copies the
field names, copies over the data, makes worksheet visible, and leaves it open

The problem I'm getting is in just one query. I created it in design view
and it works perfectly well, except when used in this function. I repeat, the
function works with every query I have with the exception of this one:

SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS UNITS
FROM SIDES
GROUP BY "10315", SIDES.OUT
HAVING (((Sum(SIDES.SidesTO))>0));


SidesTO is a calculated field using data from other queries
The query just re-names three fields, and groups the results by a numbered
cost center as long as the SidesTO is greater than zero.

Why am I getting the above error message with just this particular query???

I suspect the problem may be your choice of alias names. From is definitely
a reserved word, and it wouldn't surprise me to find out that To is as well.

If you must use those names, try putting square brackets around them:
"10315" AS [FROM], SIDES.OUT AS [TO], although renaming them would be
preferable.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ray S." <(E-Mail Removed)> wrote in message
news:14F2609B-EC90-4515-8D52-(E-Mail Removed)...
> I have a function that sends query outputs to excel...
>
> In essence, the relevant part is
>
> Sub QueryToExcel()
> ' Send Query Output results to Excel
>
> Dim rstOutput As ADODB.Recordset
> Dim objXL As Excel.Application
> Dim objWS As Excel.Worksheet
> Dim fld As ADODB.Field
> Dim intCol As Integer
> Dim intRow As Integer
>
> Set rstOutput = New ADODB.Recordset
>
> ' This is where I keep getting an error
> 'Run-time error '-2147217900 (80040e14):
> 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
> ''SELECT', or 'UPDATE'.
>
> rstOutput.Open "QueryOutput", CurrentProject.Connection
>
> the rest of the function lauches Excel, creates a worksheet, copies the
> field names, copies over the data, makes worksheet visible, and leaves it
open
>
> The problem I'm getting is in just one query. I created it in design view
> and it works perfectly well, except when used in this function. I repeat,
the
> function works with every query I have with the exception of this one:
>
> SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS UNITS
> FROM SIDES
> GROUP BY "10315", SIDES.OUT
> HAVING (((Sum(SIDES.SidesTO))>0));
>
>
> SidesTO is a calculated field using data from other queries
> The query just re-names three fields, and groups the results by a numbered
> cost center as long as the SidesTO is greater than zero.
>
> Why am I getting the above error message with just this particular
query???

Well, actually, neither of those names are the actual aliases I use. Let's
call them FROM_CC and TO_CC. My problem still exists.

"Douglas J Steele" wrote:

> I suspect the problem may be your choice of alias names. From is definitely
> a reserved word, and it wouldn't surprise me to find out that To is as well.
>
> If you must use those names, try putting square brackets around them:
> "10315" AS [FROM], SIDES.OUT AS [TO], although renaming them would be
> preferable.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ray S." <(E-Mail Removed)> wrote in message
> news:14F2609B-EC90-4515-8D52-(E-Mail Removed)...
> > I have a function that sends query outputs to excel...
> >
> > In essence, the relevant part is
> >
> > Sub QueryToExcel()
> > ' Send Query Output results to Excel
> >
> > Dim rstOutput As ADODB.Recordset
> > Dim objXL As Excel.Application
> > Dim objWS As Excel.Worksheet
> > Dim fld As ADODB.Field
> > Dim intCol As Integer
> > Dim intRow As Integer
> >
> > Set rstOutput = New ADODB.Recordset
> >
> > ' This is where I keep getting an error
> > 'Run-time error '-2147217900 (80040e14):
> > 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
> > ''SELECT', or 'UPDATE'.
> >
> > rstOutput.Open "QueryOutput", CurrentProject.Connection
> >
> > the rest of the function lauches Excel, creates a worksheet, copies the
> > field names, copies over the data, makes worksheet visible, and leaves it
> open
> >
> > The problem I'm getting is in just one query. I created it in design view
> > and it works perfectly well, except when used in this function. I repeat,
> the
> > function works with every query I have with the exception of this one:
> >
> > SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS UNITS
> > FROM SIDES
> > GROUP BY "10315", SIDES.OUT
> > HAVING (((Sum(SIDES.SidesTO))>0));
> >
> >
> > SidesTO is a calculated field using data from other queries
> > The query just re-names three fields, and groups the results by a numbered
> > cost center as long as the SidesTO is greater than zero.
> >
> > Why am I getting the above error message with just this particular
> query???
>
>
>

Perhaps you should post the actual SQL, then. It's pretty hard for anyone to
tell you what's wrong if we can't see the code...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ray S." <(E-Mail Removed)> wrote in message
news:1FB8007A-FFAF-42EE-95B7-(E-Mail Removed)...
> Well, actually, neither of those names are the actual aliases I use. Let's
> call them FROM_CC and TO_CC. My problem still exists.
>
> "Douglas J Steele" wrote:
>
> > I suspect the problem may be your choice of alias names. From is
definitely
> > a reserved word, and it wouldn't surprise me to find out that To is as
well.
> >
> > If you must use those names, try putting square brackets around them:
> > "10315" AS [FROM], SIDES.OUT AS [TO], although renaming them would be
> > preferable.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Ray S." <(E-Mail Removed)> wrote in message
> > news:14F2609B-EC90-4515-8D52-(E-Mail Removed)...
> > > I have a function that sends query outputs to excel...
> > >
> > > In essence, the relevant part is
> > >
> > > Sub QueryToExcel()
> > > ' Send Query Output results to Excel
> > >
> > > Dim rstOutput As ADODB.Recordset
> > > Dim objXL As Excel.Application
> > > Dim objWS As Excel.Worksheet
> > > Dim fld As ADODB.Field
> > > Dim intCol As Integer
> > > Dim intRow As Integer
> > >
> > > Set rstOutput = New ADODB.Recordset
> > >
> > > ' This is where I keep getting an error
> > > 'Run-time error '-2147217900 (80040e14):
> > > 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
> > > ''SELECT', or 'UPDATE'.
> > >
> > > rstOutput.Open "QueryOutput", CurrentProject.Connection
> > >
> > > the rest of the function lauches Excel, creates a worksheet, copies
the
> > > field names, copies over the data, makes worksheet visible, and leaves
it
> > open
> > >
> > > The problem I'm getting is in just one query. I created it in design
view
> > > and it works perfectly well, except when used in this function. I
repeat,
> > the
> > > function works with every query I have with the exception of this one:
> > >
> > > SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS UNITS
> > > FROM SIDES
> > > GROUP BY "10315", SIDES.OUT
> > > HAVING (((Sum(SIDES.SidesTO))>0));
> > >
> > >
> > > SidesTO is a calculated field using data from other queries
> > > The query just re-names three fields, and groups the results by a
numbered
> > > cost center as long as the SidesTO is greater than zero.
> > >
> > > Why am I getting the above error message with just this particular
> > query???
> >
> >
> >

SELECT "10315" AS FROM_CC_NBR, FinalAllocationPrintSIDES.CCOUT AS TO_CC_NBR,
Sum(FinalAllocationPrintSIDES.SidesToCC) AS ACTUAL_UNITS
FROM FinalAllocationPrintSIDES
GROUP BY "10315", FinalAllocationPrintSIDES.CCOUT
HAVING (((Sum(FinalAllocationPrintSIDES.SidesToCC))>0));

But, I don't think it will really mean much without understanding what all
the different underlying queries do...maybe I'm wrong.

"Douglas J Steele" wrote:

> Perhaps you should post the actual SQL, then. It's pretty hard for anyone to
> tell you what's wrong if we can't see the code...
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Ray S." <(E-Mail Removed)> wrote in message
> news:1FB8007A-FFAF-42EE-95B7-(E-Mail Removed)...
> > Well, actually, neither of those names are the actual aliases I use. Let's
> > call them FROM_CC and TO_CC. My problem still exists.
> >
> > "Douglas J Steele" wrote:
> >
> > > I suspect the problem may be your choice of alias names. From is
> definitely
> > > a reserved word, and it wouldn't surprise me to find out that To is as
> well.
> > >
> > > If you must use those names, try putting square brackets around them:
> > > "10315" AS [FROM], SIDES.OUT AS [TO], although renaming them would be
> > > preferable.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "Ray S." <(E-Mail Removed)> wrote in message
> > > news:14F2609B-EC90-4515-8D52-(E-Mail Removed)...
> > > > I have a function that sends query outputs to excel...
> > > >
> > > > In essence, the relevant part is
> > > >
> > > > Sub QueryToExcel()
> > > > ' Send Query Output results to Excel
> > > >
> > > > Dim rstOutput As ADODB.Recordset
> > > > Dim objXL As Excel.Application
> > > > Dim objWS As Excel.Worksheet
> > > > Dim fld As ADODB.Field
> > > > Dim intCol As Integer
> > > > Dim intRow As Integer
> > > >
> > > > Set rstOutput = New ADODB.Recordset
> > > >
> > > > ' This is where I keep getting an error
> > > > 'Run-time error '-2147217900 (80040e14):
> > > > 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
> > > > ''SELECT', or 'UPDATE'.
> > > >
> > > > rstOutput.Open "QueryOutput", CurrentProject.Connection
> > > >
> > > > the rest of the function lauches Excel, creates a worksheet, copies
> the
> > > > field names, copies over the data, makes worksheet visible, and leaves
> it
> > > open
> > > >
> > > > The problem I'm getting is in just one query. I created it in design
> view
> > > > and it works perfectly well, except when used in this function. I
> repeat,
> > > the
> > > > function works with every query I have with the exception of this one:
> > > >
> > > > SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS UNITS
> > > > FROM SIDES
> > > > GROUP BY "10315", SIDES.OUT
> > > > HAVING (((Sum(SIDES.SidesTO))>0));
> > > >
> > > >
> > > > SidesTO is a calculated field using data from other queries
> > > > The query just re-names three fields, and groups the results by a
> numbered
> > > > cost center as long as the SidesTO is greater than zero.
> > > >
> > > > Why am I getting the above error message with just this particular
> > > query???
> > >
> > >
> > >
>
>
>

Sorry, are you saying that FinalAllocationPrintSIDES is a query, not a
table?

I'm not trying to be difficult, but if you want help, you have to give us as
much information as you can!

For what it's worth, you can leave the "10315" out of the GROUP BY clause.

Also, is there a chance that FinalAllocationPrintSIDES.SidesToCC can ever be
Null? If so, you might want to use
Sum(Nz(FinalAllocationPrintSIDES.SidesToCC, 0)) instead.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ray S." <(E-Mail Removed)> wrote in message
news767F255-76CF-4659-ADC0-(E-Mail Removed)...
> SELECT "10315" AS FROM_CC_NBR, FinalAllocationPrintSIDES.CCOUT AS
TO_CC_NBR,
> Sum(FinalAllocationPrintSIDES.SidesToCC) AS ACTUAL_UNITS
> FROM FinalAllocationPrintSIDES
> GROUP BY "10315", FinalAllocationPrintSIDES.CCOUT
> HAVING (((Sum(FinalAllocationPrintSIDES.SidesToCC))>0));
>
> But, I don't think it will really mean much without understanding what all
> the different underlying queries do...maybe I'm wrong.
>
> "Douglas J Steele" wrote:
>
> > Perhaps you should post the actual SQL, then. It's pretty hard for
anyone to
> > tell you what's wrong if we can't see the code...
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Ray S." <(E-Mail Removed)> wrote in message
> > news:1FB8007A-FFAF-42EE-95B7-(E-Mail Removed)...
> > > Well, actually, neither of those names are the actual aliases I use.
Let's
> > > call them FROM_CC and TO_CC. My problem still exists.
> > >
> > > "Douglas J Steele" wrote:
> > >
> > > > I suspect the problem may be your choice of alias names. From is
> > definitely
> > > > a reserved word, and it wouldn't surprise me to find out that To is
as
> > well.
> > > >
> > > > If you must use those names, try putting square brackets around
them:
> > > > "10315" AS [FROM], SIDES.OUT AS [TO], although renaming them would
be
> > > > preferable.
> > > >
> > > > --
> > > > Doug Steele, Microsoft Access MVP
> > > > http://I.Am/DougSteele
> > > > (no e-mails, please!)
> > > >
> > > >
> > > > "Ray S." <(E-Mail Removed)> wrote in message
> > > > news:14F2609B-EC90-4515-8D52-(E-Mail Removed)...
> > > > > I have a function that sends query outputs to excel...
> > > > >
> > > > > In essence, the relevant part is
> > > > >
> > > > > Sub QueryToExcel()
> > > > > ' Send Query Output results to Excel
> > > > >
> > > > > Dim rstOutput As ADODB.Recordset
> > > > > Dim objXL As Excel.Application
> > > > > Dim objWS As Excel.Worksheet
> > > > > Dim fld As ADODB.Field
> > > > > Dim intCol As Integer
> > > > > Dim intRow As Integer
> > > > >
> > > > > Set rstOutput = New ADODB.Recordset
> > > > >
> > > > > ' This is where I keep getting an error
> > > > > 'Run-time error '-2147217900 (80040e14):
> > > > > 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
> > > > > ''SELECT', or 'UPDATE'.
> > > > >
> > > > > rstOutput.Open "QueryOutput", CurrentProject.Connection
> > > > >
> > > > > the rest of the function lauches Excel, creates a worksheet,
copies
> > the
> > > > > field names, copies over the data, makes worksheet visible, and
leaves
> > it
> > > > open
> > > > >
> > > > > The problem I'm getting is in just one query. I created it in
design
> > view
> > > > > and it works perfectly well, except when used in this function. I
> > repeat,
> > > > the
> > > > > function works with every query I have with the exception of this
one:
> > > > >
> > > > > SELECT "10315" AS FROM, SIDES.OUT AS TO, Sum(SIDES.SidesTO) AS
UNITS
> > > > > FROM SIDES
> > > > > GROUP BY "10315", SIDES.OUT
> > > > > HAVING (((Sum(SIDES.SidesTO))>0));
> > > > >
> > > > >
> > > > > SidesTO is a calculated field using data from other queries
> > > > > The query just re-names three fields, and groups the results by a
> > numbered
> > > > > cost center as long as the SidesTO is greater than zero.
> > > > >
> > > > > Why am I getting the above error message with just this particular
> > > > query???
> > > >
> > > >
> > > >
> >
> >
> >