InStr function

[debbiepy]

I am trying to add to my query a field that puts a 1 if the hw_type field contains a 9 or a 5 anywhere in the field but it only compares the first starting position of the field. Here is the code I am using:

=IIf(InStr([HW_type],5)=1,1,IIf(InStr([hw_type],9)=1,1,0))

How can I get it to search teh entire field, not just the starting position?

 

[pkailas]

=IIf(InStr([HW_type],"5")=1,1,IIf(InStr([hw_type],"9")=1,1,0))

eclose the 5 and 9 in quotes

 

[pkailas]

enclose the 5 and 9 in quotes.. you're looking for a string not a number

=IIf(InStr([HW_type],"5")=1,1,IIf(InStr([hw_type],"9")=1,1,0))

 

[debbiepy]

thank you but that didn't work either. However, I figured it out. I needed to say >= 1 because it returns the location of the occurance not a True or False 1 or 0. I simply changed the = to >= and it works.

 

[PHV]

And the pure SQL way (in the criteria cell of HW_type):
Like '*[59]*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

For a 9 or a 5 anywhere in the field wouldn't it be

SELECT ..blah .. WHERE [HW_type] Like "*5*" OR [HW_type] LIKE "*9*"





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK