Reserved word or puncutation error

[KHogwood-Thompson]

Hi,

I have the following code in a function, when I run it I get an error
message saying that:

The select statement includes a reserved word or argument name that is
mispelled or missing, or the punctuation is incorrect.

My code is as follows:

Private Function cmdExport()


Dim dbD As DAO.Database
Dim rsJOBREF As DAO.Recordset
Dim strFilespec As String
Dim strJOBREF As String
Dim strSheet As String
Dim strSQL As String

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;Database="
Const SQL2 = "FROM CVR WHERE JOBNO ="

Set dbD = CurrentDb()
Set rsJOBREF = dbD.OpenRecordset("SELECT JOBNO FROM CVR" _
, dbOpenSnapshot)
strFilespec = "C:\Folder\File.xls"

Do Until rsJOBREF.EOF
'Get JOBREF
strJOBREF = rsJOBREF.Fields("JOBNO").Value
strSheet = CStr(strJOBREF)

'Assemble the SQL query to export one jobref
strSQL = SQL1 & strFilespec & ";].[" & strSheet _
& SQL2 & strJOBREF & ";"

'export it
dbD.Execute strSQL, dbFailOnError
rsJOBREF.MoveNext
Loop

rsJOBREF.Close

End Function

Can anyone see the problem here??

The function creates a recordset that will be used to export the data to
Excel under different tabs.

 

[Jeff Boyce]

You didn't "flag" where the error was happening. You have several SELECT
statements.

Have you added a breakpoint and stepped through the execution of the
procedure to see where it hangs up?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KHogwood-Thompson]

Many thanks for this Jeff, I performed a search of microsoft.com with the
error message and found that the problem was that the module itself was
called the same name as the function!! I renamed the module and it works
perfectly.

Many thanks again .....
--
K Hogwood-Thompson

You didn't "flag" where the error was happening. You have several SELECT
statements.

Have you added a breakpoint and stepped through the execution of the
procedure to see where it hangs up?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have the following code in a function, when I run it I get an error
message saying that:

The select statement includes a reserved word or argument name that is
mispelled or missing, or the punctuation is incorrect.

My code is as follows:

Private Function cmdExport()


Dim dbD As DAO.Database
Dim rsJOBREF As DAO.Recordset
Dim strFilespec As String
Dim strJOBREF As String
Dim strSheet As String
Dim strSQL As String

Const SQL1 = "SELECT * INTO [Excel 8.0;HDR=Yes;Database="
Const SQL2 = "FROM CVR WHERE JOBNO ="

Set dbD = CurrentDb()
Set rsJOBREF = dbD.OpenRecordset("SELECT JOBNO FROM CVR" _
, dbOpenSnapshot)
strFilespec = "C:\Folder\File.xls"

Do Until rsJOBREF.EOF
'Get JOBREF
strJOBREF = rsJOBREF.Fields("JOBNO").Value
strSheet = CStr(strJOBREF)

'Assemble the SQL query to export one jobref
strSQL = SQL1 & strFilespec & ";].[" & strSheet _
& SQL2 & strJOBREF & ";"

'export it
dbD.Execute strSQL, dbFailOnError
rsJOBREF.MoveNext
Loop

rsJOBREF.Close

End Function

Can anyone see the problem here??

The function creates a recordset that will be used to export the data to
Excel under different tabs.