?
Solved

Run-time Error 3141 in Select Statement

Posted on 2010-01-07
8
Medium Priority
?
544 Views
Last Modified: 2013-11-27
In trying to run this module, I'm getting a run-time error 3141, the SELECT statement includes a reserved work or an argument name that is misspelled or missing, or the punctuation is incorrect.

The debug highlights line qdf.SQL = strSQL

Yet in the immediate window I see the results of the SQL statement. Ideas?
Public Sub InnerJoinComparisonReport()

   Dim qdf As DAO.QueryDef
   Dim strSQL As String, strQueryName As String
   Dim strTblBefore As String, strTblAfter As String

strTblBefore = InputBox("Enter the name of the first table")
strTblAfter = InputBox("Enter the name of the second table")

          
  strSQL = "SELECT " & strTblBefore & ".PPN AS BeforePPN," & strTblBefore & ".PPS AS BeforePPS," & strTblBefore & _
".PPNStatusEffDate AS BeforePPNStatusEffDate," & strTblBefore & ".JON AS BeforeJON," & strTblBefore & _
".JobTitle AS BeforeJobTitle," & strTblBefore & ".BillingCriteria AS BeforeBillCriteria," & strTblBefore & _
".ManHours AS BeforeManHrs," & strTblBefore & ".Budget AS BeforeBudget," & strTblBefore & _
".APSUnitN AS BeforeAPSUnitN," & strTblBefore & ".APSWBSN AS BeforeAPSWBSN," & strTblBefore & _
".APSDir AS BeforeAPSDir," & strTblBefore & ".APSMgr AS BeforeAPSMgr," & strTblBefore & _
".POwner AS BeforePOwner," & strTblBefore & ".CompCharge AS BeforeCompCharge," & strTblBefore & _
".ContractLbr AS BeforeContractLbr," & strTblBefore & ".AuthorizedName AS BeforeAuthName," & strTblBefore & _
".PropN AS BeforePropN," & strTblBefore & ".PLN AS BeforePLN," & strTblBefore & ".TCS AS BeforeTCS," & strTblBefore & _
".CP AS BeforeCP," & strTblBefore & ".PWPReq AS BeforePWPReq," & strTblBefore & ".SLNucQA AS BeforeSLNucQA," & strTblBefore & _
".SLProjMgr AS BeforeSLProjMgr," & strTblBefore & ".SLProgMgr AS BeforeSLProgMgr," & strTblAfter & _
".PPN AS AfterPPN," & strTblAfter & ".PPS AS AfterPPS," & strTblAfter & ".PPNStatusEffDate AS AfterPPNStatusEffDate," & strTblAfter & _
".JON AS AfterJON," & strTblAfter & ".JobTitle AS AfterJobTitle," & strTblAfter & ".BillingCriteria AS AfterBillCriteria," & strTblAfter & _
".ManHours AS AfterManHrs," & strTblAfter & ".Budget AS AfterBudget," & strTblAfter & ".APSUnitN AS AfterAPSUnitN," & strTblAfter & _
".APSWBSN AS AfterAPSWBSN," & strTblAfter & ".APSDir AS AfterAPSDir," & strTblAfter & ".APSMgr AS AfterAPSMgr," & strTblAfter & _
".POwner AS AfterPOwner," & strTblAfter & ".CompCharge AS AfterCompCharge," & strTblAfter & ".ContractLbr AS AfterContractLbr," & _
strTblAfter & ".AuthorizedName AS AfterAuthName," & strTblAfter & ".PropN AS AfterPropN," & strTblAfter & ".PLN AS AfterPLN," & _
strTblAfter & ".TCS AS AfterTCS," & strTblAfter & ".CP AS AfterCP," & strTblAfter & ".PWPReq AS AfterPWPReq," & strTblAfter & _
".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _
"FROM " & strTblBefore & " INNER JOIN " & strTblAfter & " ON " & strTblBefore & ".PPN = " & strTblAfter & ".PPN" & " WHERE " & strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

strQueryName = "qryInnerJoinBeforeAfter"

Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
On Error Resume Next
    With CurrentDb
    QueryDefs.Delete strqryInnerJoinBeforeAfter
    DoEvents
On Error GoTo 0
    Set qdf = .CreateQueryDef(strqryInnerJoinBeforeAfter)
    DoEvents
    Debug.Print strSQL
    qdf.SQL = strSQL
    
    
    
End With

        
End Sub

Open in new window

0
Comment
Question by:dlmaz
  • 5
  • 3
8 Comments
 
LVL 85
ID: 26201928
Can you show the value of your SQL AFTER it is filled? To do that enter this line immediately before the line reading "strQueryName = "qryInnerJoinBeforeAfter":

Debug.Print strSQL

This will print the value of strSQL to the Immediate window (to view that, click View - Immediate). Post that value back here, or better yet take that value and paste it into the SQL view of a query, and let Access see if it can figure out what's wrong.

To do that, build a new query, but do NOT add any tables. Then click View - SQL, paste the contents from the Immediate window there, and then try to switch to Design view. Access will complain about a syntax issue if it finds one, and will generally highlight where the problem is.
0
 

Author Comment

by:dlmaz
ID: 26202732
Here is the result from the immediate window and when I pasted it into the SQL view of a query, it stops on FROM with same error of 'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'

Don't know why though.
SELECT Dianne.PPN AS BeforePPN,Dianne.PPS AS BeforePPS,Dianne.PPNStatusEffDate AS BeforePPNStatusEffDate,Dianne.JON AS BeforeJON,Dianne.JobTitle AS BeforeJobTitle,Dianne.BillingCriteria AS BeforeBillCriteria,Dianne.ManHours AS BeforeManHrs,Dianne.Budget AS BeforeBudget,Dianne.APSUnitN AS BeforeAPSUnitN,Dianne.APSWBSN AS BeforeAPSWBSN,Dianne.APSDir AS BeforeAPSDir,Dianne.APSMgr AS BeforeAPSMgr,Dianne.POwner AS BeforePOwner,Dianne.CompCharge AS BeforeCompCharge,Dianne.ContractLbr AS BeforeContractLbr,Dianne.AuthorizedName AS BeforeAuthName,Dianne.PropN AS BeforePropN,Dianne.PLN AS BeforePLN,Dianne.TCS AS BeforeTCS,Dianne.CP AS BeforeCP,Dianne.PWPReq AS BeforePWPReq,Dianne.SLNucQA AS BeforeSLNucQA,Dianne.SLProjMgr AS BeforeSLProjMgr,Dianne.SLProgMgr AS BeforeSLProgMgr,Stephen.PPN AS AfterPPN,Stephen.PPS AS AfterPPS,Stephen.PPNStatusEffDate AS AfterPPNStatusEffDate,Stephen.JON AS AfterJON,Stephen.JobTitle AS AfterJobTitle,Stephen.BillingCriteria AS AfterBillCriteria,Stephen.ManHours AS AfterManHrs,Stephen.Budget
 AS AfterBudget,Stephen.APSUnitN AS AfterAPSUnitN,Stephen.APSWBSN AS AfterAPSWBSN,Stephen.APSDir AS AfterAPSDir,Stephen.APSMgr AS AfterAPSMgr,Stephen.POwner AS AfterPOwner,Stephen.CompCharge AS AfterCompCharge,Stephen.ContractLbr AS AfterContractLbr,Stephen.AuthorizedName AS AfterAuthName,Stephen.PropN AS AfterPropN,Stephen.PLN AS AfterPLN,Stephen.TCS AS AfterTCS,Stephen.CP AS AfterCP,Stephen.PWPReq AS AfterPWPReq,Stephen.SLNucQA AS AfterSLNucQA,Stephen.SLProjMgr AS AfterSLProjMgr,Stephen.SLProgMgr AS AfterSLProgMgr,FROM Dianne INNER JOIN Stephen ON Dianne.PPN = Stephen.PPN WHERE Dianne.PPS <> "X" And Stephen.PPS <> "X"
SELECT Dianne.PPN AS BeforePPN,Dianne.PPS AS BeforePPS,Dianne.PPNStatusEffDate AS BeforePPNStatusEffDate,Dianne.JON AS BeforeJON,Dianne.JobTitle AS BeforeJobTitle,Dianne.BillingCriteria AS BeforeBillCriteria,Dianne.ManHours AS BeforeManHrs,Dianne.Budget AS BeforeBudget,Dianne.APSUnitN AS BeforeAPSUnitN,Dianne.APSWBSN AS BeforeAPSWBSN,Dianne.APSDir AS BeforeAPSDir,Dianne.APSMgr AS BeforeAPSMgr,Dianne.POwner AS BeforePOwner,Dianne.CompCharge AS BeforeCompCharge,Dianne.ContractLbr AS BeforeContractLbr,Dianne.AuthorizedName AS BeforeAuthName,Dianne.PropN AS BeforePropN,Dianne.PLN AS BeforePLN,Dianne.TCS AS BeforeTCS,Dianne.CP AS BeforeCP,Dianne.PWPReq AS BeforePWPReq,Dianne.SLNucQA AS BeforeSLNucQA,Dianne.SLProjMgr AS BeforeSLProjMgr,Dianne.SLProgMgr AS BeforeSLProgMgr,Stephen.PPN AS AfterPPN,Stephen.PPS AS AfterPPS,Stephen.PPNStatusEffDate AS AfterPPNStatusEffDate,Stephen.JON AS AfterJON,Stephen.JobTitle AS AfterJobTitle,Stephen.BillingCriteria AS AfterBillCriteria,Stephen.ManHours AS AfterManHrs,Stephen.Budget
 AS AfterBudget,Stephen.APSUnitN AS AfterAPSUnitN,Stephen.APSWBSN AS AfterAPSWBSN,Stephen.APSDir AS AfterAPSDir,Stephen.APSMgr AS AfterAPSMgr,Stephen.POwner AS AfterPOwner,Stephen.CompCharge AS AfterCompCharge,Stephen.ContractLbr AS AfterContractLbr,Stephen.AuthorizedName AS AfterAuthName,Stephen.PropN AS AfterPropN,Stephen.PLN AS AfterPLN,Stephen.TCS AS AfterTCS,Stephen.CP AS AfterCP,Stephen.PWPReq AS AfterPWPReq,Stephen.SLNucQA AS AfterSLNucQA,Stephen.SLProjMgr AS AfterSLProjMgr,Stephen.SLProgMgr AS AfterSLProgMgr,FROM Dianne INNER JOIN Stephen ON Dianne.PPN = Stephen.PPN WHERE Dianne.PPS <> "X" And Stephen.PPS <> "X"

Open in new window

0
 
LVL 85
ID: 26208860
There's an extra comma immediately before FROM in the syntax. In your code above, on line 29, remove the last comma from that line:

".SLNucQA AS AfterSLNucQA," & strTblAfter & ".SLProjMgr AS AfterSLProjMgr," & strTblAfter & ".SLProgMgr AS AfterSLProgMgr," & _
                                                                                                                                                                                                   ^^^^^ - remove this comma

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
ID: 26209042
Note also that you do not have to delete and re-insert a query, if all you wish to do is change the SQL. You can do this:

Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.SQL = strSQL

That would be sufficient to change the SQL of the query, if that's what you're after.
0
 

Author Comment

by:dlmaz
ID: 26209635
I removed the comma and now get the run-time error at Dianne just after FROM

Progress?

Thank you.
SELECT Dianne.PPN AS BeforePPN,Dianne.PPS AS BeforePPS,Dianne.PPNStatusEffDate AS BeforePPNStatusEffDate,Dianne.JON AS BeforeJON,Dianne.JobTitle AS BeforeJobTitle,Dianne.BillingCriteria AS BeforeBillCriteria,Dianne.ManHours AS BeforeManHrs,Dianne.Budget AS BeforeBudget,Dianne.APSUnitN AS BeforeAPSUnitN,Dianne.APSWBSN AS BeforeAPSWBSN,Dianne.APSDir AS BeforeAPSDir,Dianne.APSMgr AS BeforeAPSMgr,Dianne.POwner AS BeforePOwner,Dianne.CompCharge AS BeforeCompCharge,Dianne.ContractLbr AS BeforeContractLbr,Dianne.AuthorizedName AS BeforeAuthName,Dianne.PropN AS BeforePropN,Dianne.PLN AS BeforePLN,Dianne.TCS AS BeforeTCS,Dianne.CP AS BeforeCP,Dianne.PWPReq AS BeforePWPReq,Dianne.SLNucQA AS BeforeSLNucQA,Dianne.SLProjMgr AS BeforeSLProjMgr,Dianne.SLProgMgr AS BeforeSLProgMgr,Stephen.PPN AS AfterPPN,Stephen.PPS AS AfterPPS,Stephen.PPNStatusEffDate AS AfterPPNStatusEffDate,Stephen.JON AS AfterJON,Stephen.JobTitle AS AfterJobTitle,Stephen.BillingCriteria AS AfterBillCriteria,Stephen.ManHours AS AfterManHrs,Stephen.Budget
 AS AfterBudget,Stephen.APSUnitN AS AfterAPSUnitN,Stephen.APSWBSN AS AfterAPSWBSN,Stephen.APSDir AS AfterAPSDir,Stephen.APSMgr AS AfterAPSMgr,Stephen.POwner AS AfterPOwner,Stephen.CompCharge AS AfterCompCharge,Stephen.ContractLbr AS AfterContractLbr,Stephen.AuthorizedName AS AfterAuthName,Stephen.PropN AS AfterPropN,Stephen.PLN AS AfterPLN,Stephen.TCS AS AfterTCS,Stephen.CP AS AfterCP,Stephen.PWPReq AS AfterPWPReq,Stephen.SLNucQA AS AfterSLNucQA,Stephen.SLProjMgr AS AfterSLProjMgr,Stephen.SLProgMgr AS AfterSLProgMgrFROM Dianne INNER JOIN Stephen ON Dianne.PPN = Stephen.PPN WHERE Dianne.PPS <> "X" And Stephen.PPS <> "X"

Open in new window

0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 26209740
Looks like you don't have a space between "AfterSLProgMgr" and "FROM":

AS AfterSLProjMgr,Stephen.SLProgMgr AS AfterSLProgMgrFROM Dianne
                                                                                          ^^^^ - no space here

Do you actually have tables or queries named "Diane" and "Stephen" in your database?
0
 

Author Closing Comment

by:dlmaz
ID: 31674572
Finally! It works!!! Yes, there was a table Dianne and Stephen. Also, your suggestion regarding not needing to delete the query worked as well. Thank you.
0
 
LVL 85
ID: 26210665
Have you broken out the data by "persons"? If so, this would NOT be the best way to handle this. Access is a relational database, and all related data should be stored in the same table. If you need to "tag" a record for a particular person, you should include a field in the table to do this, and not store the data in different tables.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question