Find values in string

[insane708]

Hi all,

I've a Microsoft Access query problem that need help. I'm accessing the database via Visual Basic 6 Jet engine

let's say the string value in the field as following:-

1111
1010
1234

I wanted to write a query to return any records that have the value of "1" that occur 2 times in the string value.

The result should be:-

1111
1010

Another scenario:-

let's say the string value in the field as following:-

1234
4321
1123
1210

I wanted to write a query to return any records that have the value of "1", "2", and "3" that all 3 values should occur in the string value.

The result should be:-

1234
4321
1123

I've tried "instr" or "like" but it doesn't work. Please help.

Thanks in advanced.

[vb5prgrmr]

Okay,...

*1*1* should do the trick but this is off the top of my head so you might also need a or *11* or % wildcard characters in stead of *

then... *1*2*3* or *2*1*3* or *3*1*2* or *2*3*1* or *1*3*2* or *3*1*2* or *3*2*1* or *12*3* or .... and so on

Best bet is to use Access's query designer to play around with the query and then goto sql view to copy it out from there to put into your program, at least for your more complex queries but for this I think you will also need to figure out all of your permutations so you can add them to your query. Which you can use either nested loops or a recursive function call...

Good Luck