Proper concatenation in VBA select query

try this

dim strCust as string, strTable_P as string, strTable_T as string, pPrevDate
strCust = ".CustNum"
pPrevDate = 09/26/06
strTable_P = "tblPC_09262006"
strTable_T = "tblPC_09272006"



Set rs = db.OpenRecordset("SELECT " & strTable_P & " & strCust &  ' AS CustNum , " & _
                          "'" & pPrevDate & "' AS InsertDt" & _
                          "FROM " & strTable_P & " LEFT JOIN '" & strTable_T & " '" & _
                          "ON " & strTable_P & " & strCust & " = " & strTable_T & " & strCust & "" & _
                          "WHERE '" & strTable_T & " & strCust & " & " & Is Null & "";")

You dont need all the single quotes surrounding your table names...
Give this a try:

strCust = ".CustNum"
pPrevDate = "9/26/06"
strTable_P = "tblPC_09262006"
strTable_T = "tblPC_09272006"

s = "SELECT " & strTable_P & strCust & " AS CustNum , "
s = s & "'" & pPrevDate & "' AS InsertDt"
s = s & "FROM " & strTable_P & " LEFT JOIN " & strTable_T
s = s & " ON " & strTable_P & strCust & " = " & strTable_T & strCust
s = s & " WHERE " & strTable_T & strCust & " Is Null;"

MsgBox s
Set rs = db.OpenRecordset(s)

trying to understand your query

Select tblPC_09262006.CustNum as CustNum , '09/26/06' as InsertDt
From tblPC_09262006  LEFT JOIN tblPC_09272006  ON tblPC_09262006.CustNum = tblPC_09272006.CustNum
WHERE  tblPC_09272006.CustNum IS NULL


U are selecting ALL records from tblPC_09262006
but then u link in tblPC_09272006, why do u do this if u do not select any columns from that table
A left join means show all records from tblPC_09262006 plus any of those that match tblPC_09272006


when u say u want to compare what do u want to compare against?

to find all records in tblPC_09262006 that dont exist in tblPC_09272006, u can try this
Select tblPC_09262006.CustNum as CustNum , '09/26/06' as InsertDt
From tblPC_09262006  
Where CustNum NOT IN (SELECT CustNum FROM tblPC_09272006)


here is sample code that would do the query I specified (plus yours)
To make it generic, u set dDate (eventually u can make it into a function whereby u pass in a date)
From this dDate, u know the current date and previous date so u dont need to hardcode anything


    Dim sSql As String
    Dim strCust As String
    Dim strTable_P As String, strTable_T As String
    Dim dDate As Date
    Dim sCurrDate As String
    Dim sPrevDate As String

'SET INITIAL DATE    
    dDate = Now()  
'for testing - u can also do this       dDate = DateSerial(2006,9,27)

    sCurrDate = Format(dDate, "MM/DD/YY")
    sPrevDate = Format(DateAdd("d", -1, dDate), "MM/DD/YY")

'Set fields and tablenames    
    strCust = "CustNum"
    strTable_P = "tblPC_" & Format(sPrevDate, "MMDDYYYY")
    strTable_T = "tblPC_" & Format(sCurrDate, "MMDDYYYY")
   
'Your SQL query
    sSql = "Select " & strTable_P & "." & strCust & ", '" & sPrevDate & "' as InsertDt " & _
           "From " & strTable_P & " LEFT JOIN " & strTable_T & " ON " & strTable_P & "." & strCust & " = " & strTable_T & "." & strCust & " " & _
           "where " & strTable_T & "." & strCust & " Is Null"

    Debug.Print sSql
   
'Not In query
    sSql = "SELECT " & strTable_P & "." & strCust & " as CustNum , '" & sPrevDate & "' as InsertDt " & _
           "FROM " & strTable_P & " " & _
           "WHERE " & strCust & " NOT IN (SELECT " & strCust & " FROM " & strTable_T & ")"
   
    Debug.Print sSql


Unless we know what your compare is doing, the query can then be written accordingly

Laura2112 ,

Can you explain what was wrong with the solution I posted?  I'd tested it before posting, and It looked like the SQL you said you wanted.

I didn't recognize the s = .  Nothing was wrong with it as far as I know.  I went for the closest to what I had, tested it and it worked. I stopped there.