Filter out Records

[hermannm]

Ill try to explain what i am trying to do the best i can. In our database we have one Field called notes and this has all the air strips a particular plane landed on that day. For Example YXS YVR YQZ YVR, What i am trying to do is filter out all the strips in BC. I have a list of all the airports that are listed in BC so the notes will only show out of province Strips.

I have tried the not like function but i couldn't get it to work properly.

Is there any way this can be done.
An example might clarify below are 5 records i only want one record

YXS YVR YQZ YXS
YXS YLW YKA YXS
YVR YWL AJ4 BN9
YVR YEG YYC YVR
YYD YDL BW4 YYD

I want to be able to filter these so it only shows the record with YVR YEG YYC YVR as that is out of province, each strip (ie YVR) is seperated by a space.

Thanks for the help

Cheers,

Matt.

 

[ted.martin]

Have you tried using the instr function. Look in the Access Help facility. This may do the trick. There is also mid, left and right functions too.

 

[hermannm]

I looked at this and couldnt figure out how it works. Im not too familiar with the programming but if someone explains it to me and how it will filter the records i could probably figure it out.

Thanks!!

 

[giovaanni]

Use a query with a calculated field in it and your string. In the calculated field, type something like this

IIF(instr(yourstringfield,"string") <> 0 or instr(yourstringfield,"string") <>0, 1,0)

Then in the criteria for this field, use 0. The "string" is the search string in your field that you are trying to find. If it is in your string it will have an index, which this instr function finds. If instr doesn't find your search string, it will return zero. This can be used to include or exclude records, it's up to you, use whatever you like here. Alternatively you could also create a function to handle this code and it would be easier to read than this huge nested IIF. Then all you would need to do is type in function(string) where your function would have all the strings you want to search for in it and so on.