Find values in string

[insane708]

Hi all,

I've a Microsoft Access query problem that need help.

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.

[missinglinq]

In the Query Grid create a calculated field to count the Ones:

CountOnes: Len([Target])-Len(Replace([Target],"1",""))

and pull only records (using the field's Criteria) where there are two of them:

Criteria: = 2

Similarly, to pull records with a 1 and a 2 and a 3, create three calculated fields to count the Ones, Twos and Threes

CountOnes: Len([Target])-Len(Replace([Target],"1",""))

CountTwos: Len([Target])-Len(Replace([Target],"2",""))

CountThrees: Len([Target])-Len(Replace([Target],"3",""))

and pull only records (using each field's Criteria) where there are at least one each of Ones, Twos, and Threes

Criteria > 0 (for the Criteria in each calculated field)

Welcome to Bytes!

We needed one more insane member here!

Linq ;0)>

[insane708]

Hi missinglinq,

Thanks for the reply, but I would like to access the query via Visual Basic 6 application but hit error "undefined function 'replace' in expression"

May I know if there is any workaround?