By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

VBA Problem

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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>

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.