Using an Or Statement in a Variable for Query (1 Viewer)

[Djblois]

I am using a variable in a Variable than switching it to a function and then using it in a query. However, I can't get it to work. If I type the Or statement in directly I can get it to work but If I get it through the Function then it doesnt work.

I have tried:

Chr(34) & "2" & Chr(34) & " or " & Chr(34) & "3" & Chr(34)
"2 or 3"
"2" or "3"

and many other combinations. What is the correct syntax that I need to use in the variable so it will work as an Or Statement in a query?

 

[SOS]

I've seen you trying to get this but I fail to understand what it is you are really doing and what the actual output is that you need. Forget for a moment about concatenating this all together. What is the purpose of this? Are you building a SQL WHERE Clause in code? Or what?

 

[DALeffler]

It's because Access will not parse a text variable, outside of a Set statement, no matter the syntax.

If a variable is assigned a string - MyVar = "2 or 3" - and a query is asked to return records where a field is equal to that variable, the query will return records where the field is equal to the whole string - "2 or 3" - and not records where MyField = "2" OR MyField = "3".

There are plenty of times when I wish Access would parse a variable like that until I think of all the times it would absolutely destroy the query results I was after...

hth,

 

[SOS]

Why not just build a query through code?

 

[Djblois]

The issue is I have a Query attached to a form where users can view different Appointments that are attached to one of three warehouses. Warehouse 1, 2, or 3 they can be attached to. The users have check-boxes at the top for the warehouses. So they may view 1 of them, 2 of them, or all of them at once. So what I was doing was setting up a variable that I set when the user clicks a checkbox. I don't want to create separate queries because I would need 7 different queries to do this. How would I do this then? anybody have any ideas?

 

[Atomic Shrimp]

You've got two threads going on this - the other one is here:
http://www.access-programmers.co.uk/forums/showthread.php?t=175097

 

[Djblois]

sorry but I thought people forget about that thread so I started a new thread with my current problem.

 

[SOS]

The issue is I have a Query attached to a form where users can view different Appointments that are attached to one of three warehouses. Warehouse 1, 2, or 3 they can be attached to. The users have check-boxes at the top for the warehouses. So they may view 1 of them, 2 of them, or all of them at once. So what I was doing was setting up a variable that I set when the user clicks a checkbox. I don't want to create separate queries because I would need 7 different queries to do this. How would I do this then? anybody have any ideas?

Here's a sample, see if this helps.

 

[Djblois]

ok I thought it would be simpler for me than creating 7 different queries and just switching to the query. However, this looks very complicated - any reason I should chose this over the query method?

 

[SOS]

ok I thought it would be simpler for me than creating 7 different queries and just switching to the query. However, this looks very complicated - any reason I should chose this over the query method?

Well it is a lot simpler than what you were trying to do and,

this requires only one query. I don't know what's so complicated about it. You are building the where clause for the query using an IN and just checking each checkbox to see if it needs to be added in.

And the qdf part along with the module (you would import the basSQLTools module in so you can use the ReplaceWhereClause code) just makes the query have what it needs.

If you are opening a report, rather than a query, you can actually use a generic query and build the same WHERE clause as shown in the sample and then just pass it in the OpenReport code.