MikeBAM

I'm trying to make a query but I don't know what function to use for one of my feilds.

I have an Options feild with data looking like this: (____ = blank or null)

Options:
_____
2
_____
2,3
4,5
_____
4,6,8
3
1,8
1,6,8
2,4,8
1,2,3,4


I need to know how to check which ones have a 2 or a 4 or both in the string.
So the data that comes back looks like:
2
2,3
4,5
4,6,8
2,4,8
1,2,3,4

I'm going to be making the criteria more complex then this though

HardCode

Well, if there are always single-digit items in the list (i.e. no 12 or 34), then you can use WHERE Options LIKE '*2*' OR Options LIKE '*4*'

Personally, I would set this field to one value if the list is small, using 1,2,4,8,16,32,64 ...etc so In your code you can check the single value for the bit:

This will check if the bit for 4 is "on", in other words if 4 is in the list. So if iOptions = 7 then 4 is in the list (4 + 2 + 1 = 7)

Dennis DVR

or you could use the In Keyword

MKoslof

How big is your table? If this a generally small table, definately use IN. If this is under say 200,000 records, you are fine. However, if using a large database, IN is very expensive in terms of memory and you will see some performance issues (in terms of speed).

HardCode

I don't think IN will work ... it seems that he has an un-normalized table because the field OPTIONS contains a text string of numbers that are comma separated. So, IN('2','4') should fail because the value is really '1,2,3,4'.

MKoslof

Well, actually a complicated query could be created. In SQL Server I would use a CASE statment, in Access the IIF(), something like:


There are definately ways to parse this out . Of course from VB, there are certain things you can and can't do (especially regarding the IIF statement, some native SQL Server calls, etc.).

Dennis DVR

well re-reading the question the code i posted will not fit to his requirements i didn't realize that the field content is a comma seperated, but it doesn't mean he can't use the IN keyword for his requirement (with ACCESS).

and another thing if he is using ADO then your post will fail either

perhaps i shouldn't answer when i'm busy

MikeBAM

Ya thanks HardCode.
I ended up having to do a long string of:
Like("*14*") OR Like("*13*") OR Like("*12*") OR Like("*8*") OR Like("*18*") OR Like("1") OR Like("1,*")

No that In() wouldn't work your right. (that was actually the first thing I tried which you told me about from my other post i think.)

MKoslof

Just curious, what database are you using? Is this Access? I can probably come up with a better query...but let me know if I can use the power of SQL Server or not .