Searching numbers!

[Meltdown]

Hi all,

How do I search for a particular occurance of numbers within a larger number.

Example:
ProductCode
23331000786
43244100999
88810008886
7771009999
5646461000
7771000888
6464100922

In the above product codes I want to be able to pull out any product code that has 100 in it, omitting the ones that have 1000 in them?


Regards
Melt

 

[oldaf294]

Meltdown, create a query with all the fields you need for the output. In a blank field place
Product100: InStr(1,[productcode],"100")
in the criteria section insert a >0 symbol. THe instr function gives the position of the first occurence of the string. With the > 0 it gives the location of the position of "100" in the string.

 

[Meltdown]

Hi oldaf294,

Unfortunately I'm still getting 1000's being returning when I use your solution.

Thanks for the reply,
Melt

 

[georgedwilkinson]

So you're trying to select your data from your table where your data is like '*100*' and not like '*1000*'?

How about:

select [MyData] from [MyTable] where [MyData] like '*100*' and [MyData] not like '*1000*';

Does that work?

 

[oldaf294]

I hurried and negelcted to account for "100" being part of "1000"...
jim