Code problem or SQL query problem?

R

Ray S.

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???
 
D

Douglas J Steele

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

(no e-mails, please!)


Ray S. said:
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
Click to expand...
query???
 
R

Ray S.

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 said:
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

(no e-mails, please!)


Ray S. said:
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
Click to expand...
query???
Click to expand...
 
D

Douglas J Steele

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

(no e-mails, please!)


Ray S. said:
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 said:
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

(no e-mails, please!)


Ray S. said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
query???
Click to expand...
Click to expand...
 
R

Ray S.

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 said:
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

(no e-mails, please!)


Ray S. said:
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 said:
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

(no e-mails, please!)


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???
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

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

(no e-mails, please!)


Ray S. said:
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 said:
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

(no e-mails, please!)


Ray S. said:
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.

:

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
Click to expand...
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

(no e-mails, please!)


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,
Click to expand...
copies
the
field names, copies over the data, makes worksheet visible, and
Click to expand...
leaves
it
open

The problem I'm getting is in just one query. I created it in
Click to expand...
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???
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What's wrong with my code? 12
Export single rows from a query to excel using a template 1
Invalid SQL Run-time error '-2147217900 (80040e14)' 2
Displaying a chart in a form 2
Invalid SQL statement on rst.Open to same table in 'Current' event 2
Compile Error 3
Using SQL in code instead of opening query 2
Table Linking Code Problem 9

Top