need help with syntax of SQL

[vicsaccess]

ok, I've been looking at this way to long and need another set of eyes. What is it I'm missing with this SQL? Right now I'm getting a (missing operator) in query expression. The passed variable is coming thru correct at "1" and the tabl variable is debugging correct at "1order". i have several possible recordsources and i would prefer to have this one function rather than declaring several recordsources and picking from them.

Private Function setorderrecord(num As Integer)
    Dim strord As String
    Dim tabl As String
    tabl = num & "ordert"
     Debug.Print (tabl)
    strord = ("SELECT " & _
             tabl & ".[order], " & _
             tabl & ".[CustomerID], " & _
             tabl & ".[Mill Thickness], " & _
             tabl & ".[Width], " & _
             tabl & ".[Length], " & _
             tabl & ".[Cuts], " & _
             tabl & ".[Cut_Weight], " & _
             tabl & ".[Second_Cut_Weight], " & _
             tabl & ".[Comments], " & _
             tabl & ".[bundle], " & _
            " CustomersT.[Customer] " & _
            " FROM '" & tabl & "' RIGHT OUTER JOIN CustomersT " & _
            " ON '" & tabl & "'.[CustomerID] = CustomersT.[CustomerID]; ")
    Debug.Print (strord)
    Forms!prodf.SubOrderF.Form.RecordSource = strord
    Forms!prodf.Requery
End Function

the "strord" is printing as

SELECT 1ordert.[order], 1ordert.[CustomerID], 1ordert.[Mill Thickness], 1ordert.[Width], 1ordert.[Length], 1ordert.[Cuts], 1ordert.[Cut_Weight], 1ordert.[Second_Cut_Weight], 1ordert.[Comments], 1ordert.[bundle],  CustomersT.[Customer]  FROM '1ordert' RIGHT OUTER JOIN CustomersT  ON '1ordert'.[CustomerID] = CustomersT.[CustomerID];

versus the original as

StrOrder1 = "SELECT [1OrderT].[PO/WO#], [1OrderT].CustomerID, [1OrderT].[Mill Thickness], " & _
        " [1OrderT].Width, [1OrderT].Length, [1OrderT].Cuts, [1OrderT].Cut_Weight,  " & _
        " [1OrderT].Second_Cut_Weight, [1OrderT].Comments, CustomersT.Customer, [1OrderT].bundle " & _
        " FROM CustomersT RIGHT JOIN 1OrderT ON CustomersT.CustomerID = [1OrderT].CustomerID; "

any help is appreciated.

[ssanfu]

... FROM '1ordert' RIGHT OUTER JOIN CustomersT  ON '1ordert'.[CustomerID] = CustomersT.[CustomerID];

So why are there quotes around the table name after the FROM keyword and after the ON?????
Try

... FROM 1ordert RIGHT OUTER JOIN CustomersT  ON 1ordert.[CustomerID] = CustomersT.[CustomerID];

Requires changing the code that creates the string "strord".



Be aware that the Access Gnomes get persnickety when object names begin with a number. If you MUST try to order objects, use "a_", "b_" as prefixes instead of numbers.


Edit:
Also, order and width are reserved words in Access and shouldn't be used as object names.

[vicsaccess]

Thanks Steve, i made the changes but still no luck. I think it is something to do with the variable, do to the error description is starting right after the select but i have tried everything i know maybe just not the right combination.

[mrmmickle1]

ssanfu has a good point.

Also, order and width are reserved words in Access and shouldn't be used as object names.

If you edit these field names in the Table you should be alright.

[orange]

Why so many recordsources with the same structure?
Why not use the table/query name of the recordsource as the parameter instead of num?

[vicsaccess]

I renamed order and width, no change. I finally got it to work when i completely renamed the table to a whole new name such as "tempT". As for the reason, short version is that i have a form for production entry that has a subform on it for produced orders. The attemp is to overcome some other issues, i was trying to use a few temporary tables to give the user the ability to continue working and data entry until all work was complete before having to access the BE database. Along that same reasoning was the ability to work ahead or afterwards on data entry, which is why i am attemting to use one form with a few extra temp recordsources.

[ssanfu]

I finally got it to work when i completely renamed the table to a whole new name such as "tempT".

I guess the Access Gnomes really didn't like the table name that began with a number.

[vicsaccess]

Thanks, after you said that i tried removing the number, no change, then i tried the number with a differant table name, no change, once i changed both together it worked. thanks for the help