Join expression not supported

[chanman525]

Hello everyone.

I received this database that uses multiple queries to drill down to a single result. Basically there are 4 input boxes on the form (stored on a table) to enter in status codes for exluding labels. Each query pass compared an individual field of the table to finally sort through all the status codes. For me I need more than 4 exclusions so I am attempting to rewrite this piece so that you can have one field that contains all the status code exceptions and pass that to the query via a variable in the Not In (82,96,91,95,70) format.

What i did was put the query together with all the criteria and made it a make table query (aea-tmp for now). I then put that code in a variable Strsql so that i can use the runsql command against it. When i run the query from the query window it works fine but when i hit it in the code it gives me the error "Join expression not supported".

I am not sure if this is the best method but the only one I could think of to eliminate the need for more queries and table/form fields.

This is all private sub on the form excuting the code.

    Dim StrSql As String
    Dim StrExcludeStatus As String
    Dim StrExcludeBays As String

      ' txtexcludestatus is the field name on the form. 
    StrExcludeStatus = "Not In (" & txtExcludeStatus & ")"
    StrExcludeBays = txtExcludeBays


StrSql = "SELECT dbo_t211_lbl.t211_lbl_nbr, dbo_t211_lbl.itm_num, dbo_statuscode.status, dbo_statuscode.status_desc, dbo_t211_lbl.scn_dtn_nbr, dbo_t211_lbl.t207_shp_ack_nbr, dbo_t211_lbl.cust_num, dbo_t211_lbl.add_dt, dbo_t211_lbl.upd_dm, dbo_t211_lbl.lbl_scn_dm, dbo_t211_lbl.upd_id, dbo_t211_lbl.acy_sts_cd, dbo_item.unt_dol_val, dbo_t211_lbl.lbl_seq_ctn_qty, [unt_dol_val]*[lbl_seq_ctn_qty] AS Extended_Cost, dbo_t211_lbl.cel_sts_cd INTO [aea-tmp]" & _
"FROM ([tblSorterLog-Dispatch] INNER JOIN (dbo_statuscode INNER JOIN dbo_t211_lbl ON dbo_statuscode.status = dbo_t211_lbl.status) ON [tblSorterLog-Dispatch].Label = dbo_t211_lbl.t211_lbl_nbr) INNER JOIN dbo_item ON dbo_t211_lbl.itm_num = dbo_item.itm_num" & _
"WHERE (((dbo_t211_lbl.scn_dtn_nbr) Not In (557,996)) AND ((dbo_t211_lbl.acy_sts_cd) Not In (82,96,91,95,70)) AND ((dbo_t211_lbl.cel_sts_cd)<>72));"

Currently I have the status code exceptions embeded in the code so there are no variables in the SQL string statement becasue i wanted to make sure it would work first.

I think I have it all in here and somewhat clear enough to understand. Please let me know if I need to add additional info and thanks for your time. This place rocks and I use it for all my stumbling blocks! Most cases I can find what I need but this one has proved different.

 

[lameid]

I can't believe it took me as long to see it as it did... Your concatenation is giving you problems. Queries need white space... You don't have any embeded in your string...

StrSql = "SELECT dbo_t211_lbl.t211_lbl_nbr, dbo_t211_lbl.itm_num, dbo_statuscode.status, dbo_statuscode.status_desc, dbo_t211_lbl.scn_dtn_nbr, dbo_t211_lbl.t207_shp_ack_nbr, dbo_t211_lbl.cust_num, dbo_t211_lbl.add_dt, dbo_t211_lbl.upd_dm, dbo_t211_lbl.lbl_scn_dm, dbo_t211_lbl.upd_id, dbo_t211_lbl.acy_sts_cd, dbo_item.unt_dol_val, dbo_t211_lbl.lbl_seq_ctn_qty, [unt_dol_val]*[lbl_seq_ctn_qty] AS Extended_Cost, dbo_t211_lbl.cel_sts_cd INTO [aea-tmp[red]] " & _[/red]
"FROM ([tblSorterLog-Dispatch] INNER JOIN (dbo_statuscode INNER JOIN dbo_t211_lbl ON dbo_statuscode.status = dbo_t211_lbl.status) ON [tblSorterLog-Dispatch].Label = dbo_t211_lbl.t211_lbl_nbr) INNER JOIN dbo_item ON dbo_t211_lbl.itm_num = dbo_item.itm_nu[red]m " & _[/red]
"WHERE (((dbo_t211_lbl.scn_dtn_nbr) Not In (557,996)) AND ((dbo_t211_lbl.acy_sts_cd) Not In (82,96,91,95,70)) AND ((dbo_t211_lbl.cel_sts_cd)<>72));"

The underscore is just a code line continuation operator, it doesn't put any white space in there.

 

[chanman525]

Thanks LameID, it is usually something small that gets you. Especially when you stare at the same lines for so long. Good eye and thank you for your time!