Query criteria OR

[joe55555]

helloo,

i have a select Query, everything is working very fine but i have a problem in the criteria.

under a field in the criteria part if i put:
Like 1,
Like 1 Or 2,
Like 1 Or 2 Or 3
...
everything works fine.

the condition "Like X Or Y" is related to listbox where the user can choose one or many item in the listbox.
so if the user chose two items in the lisbox the criteria should be: Like 1 or 2
if the user chose three items in the listbox the criteria should be: Like 1 or 2 or 3
my problem is that i am sending the condition to a String because i cannot send
"1 Or 2" in an Integer. and when i send the conidtion as a String it doesn't work and the criteria doesn't read the string.
the string contains: "Like 1" or "Like 1 Or 2", or "Like 1 or 2 or 3" and so on

any idea please !!!

 

[namliam]

yes if you are passing a string the filter will be by string...

Lookup Filter by Form, this will allow you much more flexibility and possibility.
See how far you get and post back here if you run into trouble.

 

[joe55555]

to be honest i didn't understand what to do :S can u please give me more details about what you means !!!

thank you

 

[namliam]

yes if you are passing a string the filter will be by string...

If you put text into a form's text box "Like 1 or 2" which by the way should be Like 1 or Like 2 to work as you expect it too (I think)... It will search in the query for "Like 1 or 2" as a text string

Lookup Filter by Form, this will allow you much more flexibility and possibility.
See how far you get and post back here if you run into trouble.

For filter by form, have a look at this thread:
http://www.access-programmers.co.uk/...08#post1300908
see if that helps you along

 

[joe55555]

i tried to do send it as a string to a string field
but the problem is still the same and i figured out that in the criteria of the query the "Or" cannot be sent as a string it must be written such as "string1" Or "string2" Or "string3" ....

so sending a string containing "string1 Or string2 Or string3" didn't work.

what i have in mind and i don't know if it is possible, is there a way to send a string such as "WHERE ......" and put it in the SQL code of the query ?

 

[namliam]

so sending a string containing "string1 Or string2 Or string3" didn't work.

Exactly what it does and exactly why it will not work.

what i have in mind and i don't know if it is possible, is there a way to send a string such as "WHERE ......" and put it in the SQL code of the query ?

Did you even read my previous posts on this? :banghead:

Originally Posted by namliam
Lookup Filter by Form, this will allow you much more flexibility and possibility.
See how far you get and post back here if you run into trouble.

For filter by form, have a look at this thread:
http://www.access-programmers.co.uk/...08#post1300908
see if that helps you along

 

[joe55555]

i attached a sample or trial version of how i'm trying to make it.
if you can please check it and see how i am doings.

thank you

 

[namliam]

Dont think you even bothered reading the thread right?
The link doesnt even seem to work, so yeah you probably didnt even try...

Please go here
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908
read it, look at the samples and see if you can work out what you need to change to get it to work

 

[ChristopherL]

In your query, where the criteria box just enter "A" Or "B".
If you use the 'like' operand you need to have a wildcard, such as like "%A" or "%B".

and to make it easy for you, here's two example with like and just common where in sql-view.

select * from mytable
where yourcolumn = '%A%' or yourfield = '%B%'

select * from mytable
where yourcolumn in ('A','B')

 

[Brianwarnock]

You are correct to say that he does not need to use Like as he is not using wild cards and that using IN would be the way to hard code the criteria, however you can not parameterize the IN operator . You could however use the Instr function and with his simple codes it would be , if the user were typing them into a textbox

Where instr( forms!myform!mytextbox, myfield)

However the user is using a list box so not sure what happens there.

 

[Brianwarnock]

Does this help

http://www.fontstuff.com/access/acctut11.htm

Brian

 

[joe55555]

Brianwarnock thanks a lot, this is exactly what i was looking for the link you posted also has a good sample with good commented VBA code everything is very clear inside it.

namliam I did read and check the link that didn't work, and the link that worked also, and thank you for your help and for taking from your time.

Thank you everyone

 

[Brianwarnock]

Glad to have helped, thanks must also go to JANR who posted that link on a similar thread some time ago, I just had to find it.

Brian