Proper concatenation in VBA select query

This is what I want. Help please? I will always build the today table, compare it to previous table and then  later append to a history table.

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

Getting the error '3078'.  Microsoft Jet can't find the input table or query 'False'.

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 & "";")

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

Laura2112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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 & "";")

mbizupNerdCommented:
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)
Rey Obrero (Capricorn1)Commented:
corrected

Dim strCust As String, strTable_P As String, strTable_T As String, pPrevDate As Date
strCust = ".CustNum"
pPrevDate = #9/26/2006#
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 ")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

rockiroadsCommented:
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
mbizupNerdCommented:
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.
Laura2112Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.