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

Syntax Error 3131 - dbs.CreateQueryDef ?

P: n/a
Alright, I'm really new to SQL and VBA and all this, so I might be
completely off course...but just tell me. I know C and Assembly, but
that doesn't help me much here.

I'm trying to create a module that creates 5 queries (that a report is
based on) based on a table that the user defines. My tables are named
by date (not my choice), for example:
-ArchivedActions05-20-05 Everything involving that seems fine.

I'm getting a syntax error at the line marked with a asterik, and I
feel like I've tried everything. Anything obvious that any of you can
spot?

P.S. No one in my office knows a thing about Access.
THANKS!


Option Compare Database

Sub UpdateMatrixQueries()

Dim Tbl As String

Dim strQueryName As String
Dim qryDef As QueryDef
Dim strSQL As String

Dim dbs As Database

Set dbs = CurrentDb()

cmdRequestDate_Click

Tbl = "-ArchivedActions" & txtDate

strQueryName = "Matrix - MLBUSA Related Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"

* Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Related Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "MLBUSA Specific Significant Issue & Plan"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[AuditName], " &
Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date], SnapActions.Finding, " _
& "SnapActions.Recommendation_1, SnapActions.Manager, " _
& "SnapActions.ManagerResponsible, SnapActions.IndRespDept, " _
& "SnapActions.StatusComments FROM " & Tbl & " " _
& "LEFT JOIN SnapActions ON " & Tbl & ".ActionID = SnapActions.ActionID
" _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') " _
& "AND ((" & Tbl & ".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".[Status])='Open')) OR (((" & Tbl & ".[Category]) "
_
& "Like '*BUSA specific*') AND ((" & Tbl &
".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".Status)='open'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

Set dbs = Nothing
End Sub

Private Sub cmdRequestDate_Click()
Dim dteDate As String
dteDate = InputBox("Please enter the date listed at the end" _
& " of the filename of the table you wish to use. Please" _
& " use the format MM-DD-YY and click OK.")
txtDate = dteDate
End Sub

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


P: n/a
Priscilla:

It appears that Access is objecting to the hyphen (-) in the following line:

Tbl = "-ArchivedActions" & txtDate

I removed the hyphen and was able to create the querydef.

One other note, if you want to name your querydef with the value in the
variable strQueryName, you should remove the quotes:

Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"priscilla.jenkins" <pr***************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Alright, I'm really new to SQL and VBA and all this, so I might be
completely off course...but just tell me. I know C and Assembly, but
that doesn't help me much here.

I'm trying to create a module that creates 5 queries (that a report is
based on) based on a table that the user defines. My tables are named
by date (not my choice), for example:
-ArchivedActions05-20-05 Everything involving that seems fine.

I'm getting a syntax error at the line marked with a asterik, and I
feel like I've tried everything. Anything obvious that any of you can
spot?

P.S. No one in my office knows a thing about Access.
THANKS!


Option Compare Database

Sub UpdateMatrixQueries()

Dim Tbl As String

Dim strQueryName As String
Dim qryDef As QueryDef
Dim strSQL As String

Dim dbs As Database

Set dbs = CurrentDb()

cmdRequestDate_Click

Tbl = "-ArchivedActions" & txtDate

strQueryName = "Matrix - MLBUSA Related Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"

* Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Related Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "MLBUSA Specific Significant Issue & Plan"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[AuditName], " &
Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date], SnapActions.Finding, " _
& "SnapActions.Recommendation_1, SnapActions.Manager, " _
& "SnapActions.ManagerResponsible, SnapActions.IndRespDept, " _
& "SnapActions.StatusComments FROM " & Tbl & " " _
& "LEFT JOIN SnapActions ON " & Tbl & ".ActionID = SnapActions.ActionID
" _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') " _
& "AND ((" & Tbl & ".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".[Status])='Open')) OR (((" & Tbl & ".[Category]) "
_
& "Like '*BUSA specific*') AND ((" & Tbl &
".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".Status)='open'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

Set dbs = Nothing
End Sub

Private Sub cmdRequestDate_Click()
Dim dteDate As String
dteDate = InputBox("Please enter the date listed at the end" _
& " of the filename of the table you wish to use. Please" _
& " use the format MM-DD-YY and click OK.")
txtDate = dteDate
End Sub
Nov 13 '05 #2

P: n/a
You might want to try this syntax:
Tbl = "[-ArchivedActions" & txtDate & "]"

"David Lloyd" <Da***@NoSpamPlease.com> wrote in message
news:lY*********************@bignews6.bellsouth.ne t...
Priscilla:

It appears that Access is objecting to the hyphen (-) in the following line:
Tbl = "-ArchivedActions" & txtDate

I removed the hyphen and was able to create the querydef.

One other note, if you want to name your querydef with the value in the
variable strQueryName, you should remove the quotes:

Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

"priscilla.jenkins" <pr***************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Alright, I'm really new to SQL and VBA and all this, so I might be
completely off course...but just tell me. I know C and Assembly, but
that doesn't help me much here.

I'm trying to create a module that creates 5 queries (that a report is
based on) based on a table that the user defines. My tables are named
by date (not my choice), for example:
-ArchivedActions05-20-05 Everything involving that seems fine.

I'm getting a syntax error at the line marked with a asterik, and I
feel like I've tried everything. Anything obvious that any of you can
spot?

P.S. No one in my office knows a thing about Access.
THANKS!


Option Compare Database

Sub UpdateMatrixQueries()

Dim Tbl As String

Dim strQueryName As String
Dim qryDef As QueryDef
Dim strSQL As String

Dim dbs As Database

Set dbs = CurrentDb()

cmdRequestDate_Click

Tbl = "-ArchivedActions" & txtDate

strQueryName = "Matrix - MLBUSA Related Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"

* Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Other"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Other')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Other'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Related Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Related*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA related*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "Matrix - MLBUSA Specific Significant"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[Quarter New], " _
& Tbl & ".[Quarter Close], " & Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date] FROM " & Tbl & " " _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant')) Or " _
& "(((" & Tbl & ".[Category]) Like '*BUSA specific*') And " _
& "((" & Tbl & ".[ActionType])='Significant'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

strQueryName = "MLBUSA Specific Significant Issue & Plan"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT " & Tbl & ".[ActionID], " & Tbl & ".[AuditName], " &
Tbl & ".[Category], " _
& Tbl & ".[ActionType], " & Tbl & ".[Status], " _
& Tbl & ".[Past Due], " & Tbl & ".[Due Date], " _
& Tbl & ".[Revised Due Date], SnapActions.Finding, " _
& "SnapActions.Recommendation_1, SnapActions.Manager, " _
& "SnapActions.ManagerResponsible, SnapActions.IndRespDept, " _
& "SnapActions.StatusComments FROM " & Tbl & " " _
& "LEFT JOIN SnapActions ON " & Tbl & ".ActionID = SnapActions.ActionID
" _
& "WHERE (((" & Tbl & ".[Category]) Like '*BUSA Specific*') " _
& "AND ((" & Tbl & ".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".[Status])='Open')) OR (((" & Tbl & ".[Category]) "
_
& "Like '*BUSA specific*') AND ((" & Tbl &
".[ActionType])='Significant') " _
& "AND ((" & Tbl & ".Status)='open'));"
Set qryDef = dbs.CreateQueryDef("strQueryName", strSQL)

Set dbs = Nothing
End Sub

Private Sub cmdRequestDate_Click()
Dim dteDate As String
dteDate = InputBox("Please enter the date listed at the end" _
& " of the filename of the table you wish to use. Please" _
& " use the format MM-DD-YY and click OK.")
txtDate = dteDate
End Sub

Nov 13 '05 #3

P: n/a
Success at last!

This:

Tbl = "[-ArchivedActions" & txtDate & "]"

....as well as getting rid of the sub function and running that code
directly from the main code worked!

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.