DAO.QueryDef

There is something very strange about your SQL string. When I dimension the strTblBefore and strTblAfter as strings and give them values, then put Debug.Print strSQL after strSQL = "SELECT " & ..., I get True instead of a string.

Public Sub InnerJoin()
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
    Dim strTblBefore As String, strTblAfter As String
    strTblBefore = "strTblBefore"
    strTblAfter = "strTblAfter"
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 AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"
 
'======== ADDED ========
Debug.Print strSQL
'=======================
 
strQueryName = "qryInnerJoinBeforeAfter"
Set qdf = CurrentDb.QueryDefs("qryInnerJoinBeforeAfter")
qdf.Sql = strSQL
Set qdf = Nothing
 
 Debug.Print strSQL
    
End Sub

Select allOpen in new window

try this

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")
qdf.SQL = strSQL
Set qdf = Nothing

 Debug.Print strSQL

For starters, your syntax with the line continuation characters (Underscores) is incorrect.

I've fixed a few lines for you to show you the correct syntax (Underscore at the end, ampersand at the beginning of the next line):
 
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

Also, you need to define strTblBefore and strTblAfter (as shown in the previous post)

Sorry cap,

I didn't refresh the screen.

cap's syntax works as well.
 
(Ignore my previous post)

dlmaz,

For the record, it's nothing to do with the line continuation characters. I'm set in my ways of doing things, so at first glance your syntax looked wrong to me (that part of the syntax in the original post is definitely correct - my remark to the contrary was wrong).

This was the original end of your sql string:
".PPS AS BeforePPS," <> "X" And strTblAfter & ".PPS AS AfterPPS," <> "X"

Cap made a great catch on this one. The change he made was very subtle:


strTblBefore & ".PPS <> ""X"" And " & strTblAfter & ".PPS <> ""X"""

He rearranged the quotes a bit dropping the quotes before the <> and adding quotes around the X's. This embedded the X in the SQL string.

Before that, the SQL string was effectively a boolean expression that checked two conditions:
1. The bulk of your SQL string <> 'X'
2. "someTableName.PPS AS AfterPPS," <> "X"

Both of these conditions are true since neither of these strings was literally 'X'. That's why the SQL string evaluated to TRUE, per the first comment.

No points for this comment please - it was an excellent find on cap's part, but worthy of an explanation.

Thank you for the quick responses, however after using the code as provided by capricorn1, I now get this error:

Run-time error '3075':
Syntax error (missing operator) in query expression '.PPN'

What I am trying to accomplish is to get an inner join query to run based upon input on the table names that are created by the user (see note below).

Please, note the SQL is based upon the following:

Public Function CreateTable()

    Dim sSql As String
    Dim sTable
   
    sTable = InputBox("Enter Table Name", "Table Creation")
    If Trim$(Nz(sTable, "")) = "" Then
        MsgBox "Must specify table name"
    Else
        sSql = "SELECT PPN, PPS, PPNStatusEffDate, JON, JobTitle, BillingCriteria, ManHours, Budget, APSUnitN, APSWBSN, APSDir, APSMgr, POwner, CompCharge, ContractLbr, AuthorizedName, PropN, PLN, TCS, CP, PWPReq, SLNucQA, SLProjMgr, SLProgMgr INTO [" & sTable & "] FROM qryBeforeAfter"

        Debug.Print sSql

        CurrentDb.Execute sSql, dbFailOnError
    End If
End Function

dlmaz,

in your query that you build, you have specified
" & strTblBefore & ".PPN AS BeforePPN

and
" & strTblAfter & ".PPN AS AfterPPN

Just to mention - readability is a wonderful thing in development.
It helps you support your own application, which helps you make your application's quality better.
While I don't believe in it at the expense of performance or functionality, when there's nothing to lose then readability every time! ;-)
A couple of table aliases and it's much simpler.
strSQL = "SELECT B.PPN AS BeforePPN, B.PPS AS BeforePPS," & _
"B.PPNStatusEffDate AS BeforePPNStatusEffDate, B.JON AS BeforeJON," & _
"B.JobTitle AS BeforeJobTitle, B.BillingCriteria AS BeforeBillCriteria," & _
"B.ManHours AS BeforeManHrs, B.Budget AS BeforeBudget," & _
"B.APSUnitN AS BeforeAPSUnitN, B.APSWBSN AS BeforeAPSWBSN," & _
"B.APSDir AS BeforeAPSDir, B.APSMgr AS BeforeAPSMgr," & _
"B.POwner AS BeforePOwner, B.CompCharge AS BeforeCompCharge," & _
"B.ContractLbr AS BeforeContractLbr, B.AuthorizedName AS BeforeAuthName," & _
"B.PropN AS BeforePropN, B.PLN AS BeforePLN, B.TCS AS BeforeTCS," & _
"B.CP AS BeforeCP, B.PWPReq AS BeforePWPReq, B.SLNucQA AS BeforeSLNucQA," & _
"B.SLProjMgr AS BeforeSLProjMgr, B.SLProgMgr AS BeforeSLProgMgr," & _
"A.PPN AS AfterPPN, A.PPS AS AfterPPS, A.PPNStatusEffDate AS AfterPPNStatusEffDate," & _
"A.JON AS AfterJON, A.JobTitle AS AfterJobTitle, A.BillingCriteria AS AfterBillCriteria," & _
"A.ManHours AS AfterManHrs, A.Budget AS AfterBudget, A.APSUnitN AS AfterAPSUnitN," & _
"A.APSWBSN AS AfterAPSWBSN, A.APSDir AS AfterAPSDir, A.APSMgr AS AfterAPSMgr," & _
"A.POwner AS AfterPOwner, A.CompCharge AS AfterCompCharge, A.ContractLbr AS AfterContractLbr," & _
"A.AuthorizedName AS AfterAuthName, A.PropN AS AfterPropN, A.PLN AS AfterPLN," & _
"A.TCS AS AfterTCS, A.CP AS AfterCP, A.PWPReq AS AfterPWPReq," & _
"A.SLNucQA AS AfterSLNucQA, A.SLProjMgr AS AfterSLProjMgr, A.SLProgMgr AS AfterSLProgMgr " & _
"FROM " & strTblBefore & " B INNER JOIN " & strTblAfter & " A ON B.PPN = A.PPN WHERE B.PPS <> 'X' And A.PPS <> 'X'"

Miriam:  I've had & at the end of a line on countless occasions.

Please, excuse the delayed response but I've had other issues at work that took priority over this until now. Reviewing the comments since my last post, I still haven't been able to resolve this issue.

Now, there was one module created to allow the user to name the table being created from essentially a Make Table query.

The second module that I'm working on will run the Inner Join query based upon the 2 table names supplied by the user.

For example, the user specifies a table name of Smith and another table name of Jones. When the Inner Join query is run, it should ask the user which table name will be considered tblBefore? and which table name will be considered tblAfter?

Hopefully, this will help clarify what it is I am trying to accomplish. If it's still not clear or if the code used so far will not accomplish this, please, feel free to advise me. Thank you!

I'm not entirely clear. Which part are you still struggling with? Asking the user? Using their entries? The SQL itself should be OK - no?

Using the attached, I'm still getting a Run-time error 'syntax error (missing operator) in query expression '.PPN'.'

Also, I discovered yesterday afternoon that when the SQL did work with input on the table names, the query is not using the data from those tables anyway.

I just don't seem to get it!

I don't know what I'm doing wrong or if I'm making it more complicated than it should be.

ublic Sub InnerJoin()
 
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strQueryName As String
        
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")
qdf.SQL = strSQL
Set qdf = Nothing
 
 Debug.Print strSQL
 
    
End Sub

Select allOpen in new window

Replace lines 27 through 29 with the code in the snippet.
-Chuck

' turn off error handling
On Error Resume Next
    With CurrentDb
        ' delete any the query if it exists
        .QueryDefs.Delete strQueryName
        DoEvents
' turn error handling back on
On Error GoTo 0
        ' create a new query
        Set qdf = .CreateQueryDef(strQueryName)
        DoEvents
        ' set the sql of the new query
        qdf.Sql = strSQL
        DoEvents
        ' run the query,
        qdf.Execute
        DoEvents
        ' delete the query
        .QueryDefs.Delete strQueryName
        DoEvents
    End With

Select allOpen in new window

Thank you for the quick response but now I get Run-time error '3065'; Cannot execute a select query. The query itself "qryInnerJoinBeforeAfter" is an inner join query....?