Variable in SQL String

[chanman525]

Hello to all again,

I have an sql statement that is not interpreting a variable correctly.

Here is the code;

    'these two (txt*)are referencing fields in a form and not defined in the code, just wanted to show the values.
    txtExcludeStatus = 82,91,95,96
    txtExcludeBays = 457,557,996

    StrExcludeStatus = "Not In (" & txtExcludeStatus & ")"
    StrExcludeBays = "Not In (" & txtExcludeBays & ")"

StrSql = "SELECT dbo_t211_lbl.t211_lbl_nbr, dbo_t211_lbl.itm_num, dbo_t211_lbl.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 [tblSorterLog_Dispatch_Final]"
StrSql = StrSql & " 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"
StrSql = StrSql & " WHERE (((dbo_t211_lbl.status)[COLOR=red] " & StrExcludeStatus & "[/color] ) AND ((dbo_t211_lbl.scn_dtn_nbr) [COLOR=red] " & StrExcludebay & " [/color] )AND ((dbo_t211_lbl.acy_sts_cd)<>70) AND ((dbo_t211_lbl.cel_sts_cd) Not In (70,71,72)));"

DoCmd.RunSQL (StrSql)

When i use a regular query using the above statement and put in the value of dbo_t211_lbl.status criteria 'Not In (81,92,96,95)' and dbo_t211_lbl.scn_dtn_nbr 'Not In (457,557,996)' the data comes back correctly however it does not when used in the sql string. I assume there is an issue with quotes or something but I have tried different combinations to no avail.

Any thoughts and help would be appreciated and let me know if I need to post more info.

Thanks!

 

[dhookom]

How about telling us something about your results? Do you get a syntax error or just the wrong data?

Have you tried adding a line to print strSQL to the debug window?

debug.print StrSql
DoCmd.RunSQL (StrSql)

Copy the results in the debug window to a new, blank query.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

I notice there is no space between the closing paranthesis and the AND:

& StrExcludebay & " )AND ((dbo_t211_lbl.acy_sts_cd)<>70)

 

[chanman525]

Dhookom,
There is no syntax error and it runs the query just fine, it is just the wrong data. What happens is that the bays that are being excluded are still being returned in the table created by the query. However when I run the query via the GUI it excludes the bays as intended.

I have done a msgbox on the variable strExcludeBays and it returns the correct structure of ' Not In(457,557,996) ' so I know that the variable is being built correctly. I just wonder if there is something in the quoting of the variable in the string.

JoeAtWork, that is just from me adding in the HTML code to produce the red lettering, the string is correct and does not produce any errors.

 

[dhookom]

I don't know if you have tried my suggestion or not since you didn't provide an answer that I could detect. When someone provides a suggestion, especially one that is easy to implement, you should at least try it and respond that it helped or didn't help.

Have you tried adding a line to print strSQL to the debug window?

debug.print StrSql
DoCmd.RunSQL (StrSql)

Copy the results in the debug window to a new, blank query.

Duane
Hook'D on Access
MS Access MVP

 

[chanman525]

Dhookom,
debug.print did work and I found where the error was. Thanks for your expertise.

 

[kjv1611]

chanman525,

Where was the error? Can you share?

And was dhookom's suggestion truly helpful? If so, you can make sure others know that you thought so by clicking "Thank dhookom for this valuable post!"

Thanks,
kjv

--

"If to err is human, then I must be some kind of human!" -Me

 

[chanman525]

Kjv1611,
the debug.print outputs the actually SQL string as it is interpreted and I realized that there was a mispelling on the variable in the string. Actually off by one character and that makes it very tough to admit when you search so long for the issue and dont think of something as simple as a debug.print to check the outputs.

 

[kjv1611]

True - those typos can be loads of fun, particularly with any sort of code!

--

"If to err is human, then I must be some kind of human!" -Me