VBA Problem

[Mike Dailey]

I am trying to run a query in VBA based upon a query in Access. While
I have pulled out much of the original query while debugging, I still
am getting an Error in SELECT statement (error 3141) when attempting to
run it. I have pasted the query below:

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset
Dim MySQL As String

Set MyDB = CurrentDb

MySQL = "SELECT [qry_Base_Extract].Aging_Load_Date,
[qry_Base_Extract].Group_Long_Name,
[qry_Base_Extract].Group_Short_Name, " & _
"[qry_Base_Extract].[FSC Name], [qry_Base_Extract].[FSC_#],
[qry_Base_Extract].Period_Created, [qry_Base_Extract].Inv_Created_Date,
" & _
"[qry_Base_Extract].Inv_Svc_Date, [qry_Base_Extract].Inv_Num,
[qry_Base_Extract].Inv_Amt, " & _
"FROM [qry_Base_Extract];"
Debug.Print MySQL

Set MyRst = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)

....I'm sure I'm missing something simple. Any assistance would be
appreciated!!! Thanks.

[pietlinden]

You've got a comma here where it doesn't belong:

"[qry_Base_Extract].Inv_Svc_Date, [qry_Base_Extract].Inv_Num,
[qry_Base_Extract].Inv_Amt, " & _
"FROM [qry_Base_Extract];"

There should be no comma between your SELECT ... and your FROM
statements. (so no comma after [qry_base_extract].Inv_Amt

One way to do this that's MUCH easier is to have the QBE build your
query, then if you hit the SQL button, you can copy and paste. The one
thing to remember, though, is that you have to fix the single quotes.

[Linda Burnside]

Hello,

Try rewriting your query without the extra "table" name before each field.
I know that Access puts this in by default when building a query, but it's
kind of cluttered when looking at vba code. Also, I don't think you need
the semi colon at the end.

"SELECT Aging_Load_Date,Group_Long_Name, Group_Short_Name, " & _
"[FSC Name], [FSC_#], Period_Created, Inv_Created_Date," & _
"Inv_Svc_Date, Inv_Num, Inv_Amt, " & _
"FROM qry_Base_Extract"

Linda
"Mike Dailey" <mi*********@ucphysicians.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I am trying to run a query in VBA based upon a query in Access. While
I have pulled out much of the original query while debugging, I still
am getting an Error in SELECT statement (error 3141) when attempting to
run it. I have pasted the query below:

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset
Dim MySQL As String

Set MyDB = CurrentDb

MySQL = "SELECT [qry_Base_Extract].Aging_Load_Date,
[qry_Base_Extract].Group_Long_Name,
[qry_Base_Extract].Group_Short_Name, " & _
"[qry_Base_Extract].[FSC Name], [qry_Base_Extract].[FSC_#],
[qry_Base_Extract].Period_Created, [qry_Base_Extract].Inv_Created_Date,
" & _
"[qry_Base_Extract].Inv_Svc_Date, [qry_Base_Extract].Inv_Num,
[qry_Base_Extract].Inv_Amt, " & _
"FROM [qry_Base_Extract];"
Debug.Print MySQL

Set MyRst = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)

...I'm sure I'm missing something simple. Any assistance would be
appreciated!!! Thanks.

[Tom van Stiphout]

On Thu, 09 Jun 2005 20:01:44 GMT, "Linda Burnside"
<linda@nospam_burnsidebiz.com> wrote:

That advice will work if the field names are not ambiguous.
The semicolon is indeed not needed, but harmless.
-Tom.

Hello,

Try rewriting your query without the extra "table" name before each field.
I know that Access puts this in by default when building a query, but it's
kind of cluttered when looking at vba code. Also, I don't think you need
the semi colon at the end.

<clip>