Is Like/Not Like options

C

Contro

Hi guys!

I've ran into a problem. I'm basically trying to find the number of
rows which contain values of certain decades. They can be in the
formats

70, 60, 90

1990, 1995, 2000

or maybe even

70, 1990, 20, 1980

Basically I want the user to be able to enter the decade. So they'd
enter either "9" and that would pick up entries of either 1990 and 95.
The problem is making it pick up both that kind of entry with no
problems. It's perfectly fine for other decades bar 1990, but the
code, no matter how hard I try, keeps picking up entries for 1980 (any
year with 19 at the front) as well as 90 when I search for decades
inputting "9".

Here is a snip of my code:

WHERE (((Applications.[Year(s) of Qualification] LIKE "* " & [choice] &
"#*" OR Applications.[Year(s) of Qualification] LIKE "*," & [choice] &
"#*")
AND
Applications.[Year(s) of Qualification] NOT LIKE "* #" & [choice] &
"##*" OR Applications.[Year(s) of Qualification] NOT LIKE "*,#" &
[choice] & "##*")
OR
Applications.[Year(s) of Qualification] LIKE "* #" & [choice] & "##*"
OR Applications.[Year(s) of Qualification] LIKE "*,#" & [choice] &
"##*");

This seems overly complicated, and I'm sure there is an easier way, but
I just don't know how. Some kind of OR condition function available in
the LIKE part or something...if you could help, that would be great!
The code above doesn't work as it logically isn't sound. But I don't
know how else to try it!

I hope it makes sense. It's kind of hard to explain, but basically
when searching for decades in the 90's, it picks up 1980 or 1975 or
1955 (picking up the nine).

Thank you so much in advance!

Contro.
 
J

Jeff Boyce

If by "decades" you mean a ten-year band starting with x0 and ending with
x9, you'd need to specific that the third character of the year was the
number you are seeking (e.g., looking for "9" to find the 90's).

You could use wildcard characters in a criterion of your query. Something
like:

Like "##" & [?decade] & "#"
to prompt for the decade number. Note that this requires that you "feed" it
the year -- if you have a date/time field, you could use Year([YourField])
to start with the year.
 
C

Contro

Hi There!

Thanks very much for your help. Unfortunately, I can't modify the
table layout, as it's an already existing table filled with data (yes,
I know it's dirty and noisy data, but it wasn't designed properly).

Is there any other way around the problem, using code that is probably
a bit too complicated than is necessary?

Thanks again, and thank you for your patience!

Contro.
 
C

Contro

I think that is what I've already done in my sample text isn't it? Or
something similar anyway..but what does the ? in [?decade] do exactly?


The problem I have is that I want to pick up dates containing a
specified decade that are 4 digit as well as 2 digit (ie 1990 or 90, as
well as fields with multiple years in them (I can't change the layout
or data in the tables either unfortunately).

The problem I'm having though is that entering "9" will usually pick up
both 1980 as well as the specified 1990 and 90.

Sorry about all this. Thank you so much for your help!

Contro.
 
J

John Spencer

If you always have the commas separating multiple values then try

Field: YourField & ","
Criteria: Like "*" & [Decade Number] & "?,*"

You append a comma to the end of the field so that all the numbers are
followed by a comma. so 70,60,90,

The criteria is any number of characters followed by the decade, followed by
one character and then a comma followed by any number of characters.
 
J

Jeff Boyce

The ? is not significant -- I just put that in to help prompt the user to
enter a decade.

I had not realized that you have multiple values in a single field. You
will probably end up having to parse out the entire string before looking
for "decade" information, since the pattern/mask I suggested would only work
if your field contained a single value per record.

I foresee some coding in your future!

Good luck

Jeff Boyce
<Office/Access MVP>
 
Top