asked on

Run-time Error 3141 in Select Statement

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

Scott McDaniel (EE MVE )

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.

dlmaz

ASKER

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

Scott McDaniel (EE MVE )

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

Scott McDaniel (EE MVE )

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.

dlmaz

ASKER

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmaz

ASKER

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.

Scott McDaniel (EE MVE )

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.