Query Filter 'masks' 1

[Loomah]

hi
i know i'm using the word mask (in the subject) in not quite the right context here but...

in access 2003 i would like to exclude a set of records from a query but can't quite get my head around what kind of criteria i need to use.

the format of the values to exclude is consistent and equates to
01AA/123456 or 02AA/123456
where AA can be any 2 letter combination and 123456 could be any 6 digit number (no more & no less than 6) so length is always 11.

The issue i'm struggling with is that access is reading (rightly so) the whole thing as text.

the initial filter i have used is
not like "01??/??????" and not like "02??/??????"
as using a # wildcard doesn't work.

This nearly gives me the results i want but the data includes (at least) one field in the format 01AA/123/56 and would, of course, allow for 01AA/abcdef too so can't be trusted.

any help/pointers would be much appreciated
thanks

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Andrzejek]

Your statement: not like "01??/??????" and not like "02??/??????"
should exclude the record 01AA/123/56 because:[tt]
01??/??????
01AA/123/56[/tt]
it does fit the criteria, unless you have some spaces after the /56

Have fun.

---- Andy

 

[PHV]

What about this ?
NOT Like "0[12][A-Z][A-Z]/######"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Loomah]

thanks guys but...
andy
that's exactly what i would have thought but the fact remains that it didn't work (for me)! i expect the reason is that the '?' wild card (ansi-86) is any single alphabetic character and i'm guessing '/' fits that criteria, unless the fact the whole thing is one string screws everything up! which brings me to...

phv
again i thought using the '#' wildcard was the way but access doesn't seem to see the data as a number - it isn't xl is it?!!
i hadn't thought of the 0[12][a-z] etc route but i did try using [1-9] 6 times for the second numeric part of the field but that threw up some strange results!

as i type this i might have another idea but....

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

And this ?
NOT Like "0[12][A-Z][A-Z]/[0-9][0-9][0-9][0-9][0-9][0-9]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Loomah]

phv
was just about to post back - that was the other idea i had in my last post!!!
have just tried it and it is looking good but still have to test the results from it more thoroughly!

thanks for the input, as always

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Andrzejek]

I know it is not what you want to do (I may be wrong...) but have you considered instead of data like you have now:
[tt]
01AA/123456
02AA/123456
01AA/123/56
[/tt]
have these in more than one column, maybe something like:
[tt]
Fld1 Fld2 Fld3 Fld4 Fld5 Fld6
01 AA / 123 4 56
02 AA / 123 4 56
01 AA / 123 / 56
[/tt]
It would be easier to deal with, IMHO

Have fun.

---- Andy

 

[Loomah]

PH - thanks again!

Andy
I get what you're saying. However the data comes from a 'live' system via a download and already has access bursting at the seems! adding another 5 fields for the sake of 1 report from a dataset that should be transfered to sql server (and will be at some point) isn't practical.

cheers!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​