Use of TempVars in SQL Statement

[Stang70Fastback]

So I have the following statement:

SELECT *
FROM ShiftsBusesSC
WHERE (((ShiftsBusesSC.[Day Of Week])=TempVars!Day) And ((ShiftsBusesSC.Category) In (TempVars!X1,TempVars!X2)) And ((ShiftsBusesSC.[Employee Last Name]) Is Null Or (ShiftsBusesSC.[Employee Last Name])<>"ACCOUNT PENDING"));

Those TempVars (in bold) are defined using the following code before being referenced, and there is no issue with anything. It all works fine.

TempVars.Add "X1", "E"
TempVars.Add "X2", "ETR"

However, I also have this statement:

SELECT ShiftChangeX.[Employee Number], ShiftChangeX.Category, ShiftChangeX.[Shift Description], ShiftChangeX.Date, ShiftChangeX.[Start Time], ShiftChangeX.[End Time], ShiftChangeX.Duration, ShiftChangeX.[Day Of Week], ShiftChangeX.[Employee First Name], ShiftChangeX.[Employee Last Name], ShiftChangeX.Route, ShiftChangeX.Bus FROM ShiftChangeX LEFT JOIN ShiftChangeY ON ShiftChangeX.[Employee Number] = ShiftChangeY.[Employee Number] WHERE (((ShiftChangeY.[Employee Number]) Is Null) AND (TempVars!X3)) UNION SELECT ShiftChangeY.[Employee Number], ShiftChangeY.Category, ShiftChangeY.[Shift Description], ShiftChangeY.Date, ShiftChangeY.[Start Time], ShiftChangeY.[End Time], ShiftChangeY.Duration, ShiftChangeY.[Day Of Week], ShiftChangeY.[Employee First Name], ShiftChangeY.[Employee Last Name], ShiftChangeY.Route, ShiftChangeY.Bus FROM ShiftChangeY LEFT JOIN ShiftChangeX ON ShiftChangeY.[Employee Number] = ShiftChangeX.[Employee Number] WHERE (((ShiftChangeX.[Employee Number]) Is Null) AND (TempVars!Y3));

In this case, those two TempVars are defined using the following piece of code (X3 in this example):

TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*12E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*21E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*32E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*40E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*50E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*80E*" & Chr(34)

I realize this is slightly more complex, but I know that the resulting value of the TempVar is EXACTLY what would normally be in the select statement. Every single character is identical, and it worked before. However, in this case, the resulting report seems to ignore this part. You can see here by the "not like" statements that I am attempting to exclude a few records from the result, but they are not excluded, so it makes me think I'm doing something wrong that causes Access to gloss over this last part. I get no errors, but it simply doesn't seem to do anything with the TempVar at all. Does anyone know why this works fine in my first example, but not in my second?

 

[boblarson]

If you are using code, why use TempVars? They are something for macros and not needed for VBA.

 

[Stang70Fastback]

I'm attempting to avoid having a crap-load of queries, because this software is eventually going to be maintained by people who are NOT IT people, lol. Using TempVars allows me to have one query that can cover a range of potential applications (for example, there are shifts from A to F, and I can use one query to filter out each set of shifts individually depending on what the X1 variable is set to, rather than having six queries each for A, B, C, D, E and F.) This way, they can just edit the variable in the Form code rather than having to edit all the queries.

Additionally, I was attempting to use a union query as a filter for my report based on which option they selected, but union queries don't seem to work as filters, only as a record source. So instead I decided to have one union query that can vary based on the variables, and set that as the record source for the report. The alternative would be to have six reports with six separate union queries, but that's 12 objects to keep up to date rather than two...

Does that make sense?

 

[boblarson]

You can do the same using a function. No need to use TempVars.

 

[Stang70Fastback]

Would you be able to elaborate on that for me to get me pointed in the right direction? I'm very new to SQL, VBA and Access (as you've no doubt figured out) so I could use a bit of a head start, as I'm not sure what you mean (I know what a function is, but I don't know what you mean by using one to solve my problem.)

I edited the post above to explain a bit better, by the way.

Thanks for the quick responses!

 

[boblarson]

There are a couple of ways you can do it. For example, you can use code for the whole SQL String, or you can store the SQL String in a table and pull it and use it at the time you need. Or you can have a query and replace parts using a querydef object. Access MVP Armen Stein has some cool code which I use for replacing the WHERE clause on queries but he's got a lot more. I can point you to that if you want. There are other ways too. But I think before I point you down a particular path, can you elaboarate a little more on how you see this query being used, how it will be invoked, etc.? I apologize if you already went through it but I'm

 

[Stang70Fastback]

You asked for it, lol, so here is what I have. Feel free to laugh at my stupidity, as I am a complete n00b.

First, the code that is run when the user clicks "print":

'Prints an A-B Shift Change run sheet.

If [ShiftChangeAB] Then
TempVars.Add "X1", "A"
TempVars.Add "X2", "ATR"
TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*20AT*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*60AT*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*90AT*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*AT2*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*AT3*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*AT4*" & Chr(34)
TempVars.Add "Y1", "B"
TempVars.Add "Y2", "BTR"
TempVars.Add "Y3", "(ShiftChangeY.[Shift Description]) Not Like " & Chr(34) & "*50B*" & Chr(34) & " And (ShiftChangeY.[Shift Description]) Not Like " & Chr(34) & "*52B*" & Chr(34)
DoCmd.OpenReport "ShiftChange", acViewReport, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "ShiftChange", acSaveNo
End If

'Prints a B-C Shift Change run sheet.

If [ShiftChangeBC] Then
TempVars.Add "X1", "B"
TempVars.Add "X2", "BTR"
TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*22B*" & Chr(34)
TempVars.Add "Y1", "C"
TempVars.Add "Y2", "CTR"
TempVars.Add "Y3", "(ShiftChangeY.[Shift Description]) Not Like " & Chr(34) & "*100C*" & Chr(34)
TempVars.Add "Z1", "A"
TempVars.Add "Z2", "ATR"
TempVars.Add "Z3", "(ShiftChangeZ.[Shift Description]) Like " & Chr(34) & "*60AT*" & Chr(34) & " Or (ShiftChangeZ.[Shift Description]) Like " & Chr(34) & "*AT2*" & Chr(34) & " Or (ShiftChangeZ.[Shift Description]) Like " & Chr(34) & "*AT3*" & Chr(34)
DoCmd.OpenReport "ShiftChange", acViewReport, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "ShiftChange", acSaveNo
End If

'Prints a C-D Shift Change run sheet.

If [ShiftChangeCD] Then
TempVars.Add "X1", "C"
TempVars.Add "X2", "CTR"
TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*100C*" & Chr(34)
TempVars.Add "Y1", "D"
TempVars.Add "Y2", "DTR"
TempVars.Add "Y3", ""
DoCmd.OpenReport "ShiftChange", acViewReport, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "ShiftChange", acSaveNo
End If

'Prints a D-E Shift Change run sheet.

If [ShiftChangeDE] Then
TempVars.Add "X1", "D"
TempVars.Add "X2", "DTR"
TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*11D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*23D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*25D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*27D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*29D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*41D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*42D*" & Chr(34) & _
" And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*52D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*60D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*62D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*72D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*82D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*90D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & _
"*91D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "92D*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*100*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*DT1*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*DT2*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*DT3*" & Chr(34)
TempVars.Add "Y1", "E"
TempVars.Add "Y2", "ETR"
TempVars.Add "Y3", ""
DoCmd.OpenReport "ShiftChange", acViewReport, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "ShiftChange", acSaveNo
End If

'Prints an E-F Shift Change run sheet.

If [ShiftChangeEF] Then
TempVars.Add "X1", "E"
TempVars.Add "X2", "ETR"
TempVars.Add "X3", "(ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*12E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*21E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*32E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*40E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*50E*" & Chr(34) & " And (ShiftChangeX.[Shift Description]) Not Like " & Chr(34) & "*80E*" & Chr(34)
TempVars.Add "Y1", "F"
TempVars.Add "Y2", "FTR"
TempVars.Add "Y3", ""
DoCmd.OpenReport "ShiftChange", acViewReport, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, "ShiftChange", acSaveNo
End If

Then, we have the code for the union query that the "ShiftChange" report uses:

SELECT ShiftChangeX.[Employee Number], ShiftChangeX.Category, ShiftChangeX.[Shift Description], ShiftChangeX.Date, ShiftChangeX.[Start Time], ShiftChangeX.[End Time], ShiftChangeX.Duration, ShiftChangeX.[Day Of Week], ShiftChangeX.[Employee First Name], ShiftChangeX.[Employee Last Name], ShiftChangeX.Route, ShiftChangeX.Bus FROM ShiftChangeX LEFT JOIN ShiftChangeY ON ShiftChangeX.[Employee Number] = ShiftChangeY.[Employee Number] WHERE (((ShiftChangeY.[Employee Number]) Is Null) AND ([TempVars]![X3])) UNION SELECT ShiftChangeY.[Employee Number], ShiftChangeY.Category, ShiftChangeY.[Shift Description], ShiftChangeY.Date, ShiftChangeY.[Start Time], ShiftChangeY.[End Time], ShiftChangeY.Duration, ShiftChangeY.[Day Of Week], ShiftChangeY.[Employee First Name], ShiftChangeY.[Employee Last Name], ShiftChangeY.Route, ShiftChangeY.Bus FROM ShiftChangeY LEFT JOIN ShiftChangeX ON ShiftChangeY.[Employee Number] = ShiftChangeX.[Employee Number] WHERE (((ShiftChangeX.[Employee Number]) Is Null) AND ([TempVars]![Y3]));

That's everything. Would you like me to explain it? I can, but you can probably figure it out faster than I could explain it to you.

I would ignore everything up to the part I have in BOLD, as that is the one I am trying to get working. The rest likely have the same issue, but the E-F shifts are simpler as there are less records to deal with.

 

[Stang70Fastback]

So it turns out I'm a MASSIVE idiot. After two months of various versions of this setup, I just now realized that all these individual lists of shifts that I have that need to be ignored at different times all have one thing in common in the description, meaning that I can just universally exclude the "(E)" designation and my problem is solved.

So I appreciate all your help, but it turns out I was just making this 100x more complicated than necessary. *smacks forehead*