Is Like/Not Like options

[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.

 

[RobFMS]

I would suggest the following:

1. Create a table that contains:
ID (Autonumber)
YearText Text(15)
YearHight Number (Integer)
YearLow Number (Integer)
SortOrder Number (Integer)

In the table put:

YearText YearLow YearHigh SortOrder
------------------------------------------------------------
1940 - 1949 1940 1949 1
1950 - 1959 1950 1959 2
1960 - 1969 1960 1969 3
1970 - 1979 1970 1979 4
:
:
(etc...)

2. Create an input form that contains a drop-down list.
In this drop-down list, use the table above as the rowsource

Rowsource: Select YearText, YearLow, YearHigh
From <tablename>
OrderBy SortOrder

Widths: 1; 0; 0


3. When the user makes a selection, they are selecting based on the YearText
information.
In the AfterUpdate() event, the you have the begin year and end year
available.

<dropdowncontrolname>.Column(1) = YearLow
<dropdowncontrolname>.Column(2) = YearHigh


4. For your query, modify is so that it uses the BETWEEN operator:

Select * From <sometable>
Where <SomeDate> Between [<dropdowncontrolname>.Column(1)] AND
[<dropdowncontrolname>.Column(2)]


This will help make for a cleaner user experience.

HTH

Rob

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

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.

 

[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.

 

[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.

 

[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.

 

[RobFMS]

Contro

There is no need to change the existing table structure, you just need to
create a new table that contains this information.

The reason for this is to provide a cleaner way to handle input for the
user. You are relying on the user to provide you correct input for the
decade.

YearText YearLow YearHigh SortOrder
------------------------------------------------------------
1940 - 1949 1940 1949 1
1950 - 1959 1950 1959 2
1960 - 1969 1960 1969 3
1970 - 1979 1970 1979 4


Let's go through an example. If the user selects, "1960 - 1969" from the
drop-down list.

You can get the following information:

1. The second column of the drop-down list contains the low year of 1960.
2. The third column of the drop-down list contains the high year of 1969.
3. Not sure how you would assume 1900 vs 2000 year but if you perform some
math,
you can infer 60 and 69 and then use a comparison to see if your 2 digit
number falls
between 60 and 69.


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/

 

[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.

 

[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>