Query excluding

I have a table that has a column name type i would like to exclude any anything that has more that two letter than end in y.the data in the column is looks like this
Y
DY
FY
Hy
EY
AY
i would like to filter it in access and excel.this would give me greater flexibility
wwstudioincAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
To exclude where it has MORE than 2 characters, AND ends with Y, use this

Criteria for Line:     Not Like "*??Y"

This will remove only the "BBY" s because they are 3 char and end with Y
0
 
nike_golfCommented:
Can you explain a little better what you are trying to do? Are you working in Access or Excel? VBA or Excel functions?

NG,

0
 
nike_golfCommented:
If its Access you can use something like should work.

Like "?y*" or Like "?Y*"

or

Like "?[yY]*"

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
wwstudioincAuthor Commented:
Sorry my mistake it should be excluding Y
0
 
cyberkiwiCommented:
Exclude where ends with Y, and the column is at least 2 characters long

Select * from Tbl where NOT Col Like "*?y"
0
 
cyberkiwiCommented:
In Excel, let's say your data is in A and starts from A2 downwards. You can insert a new column in B, and use this formula for B2 (copy down)

=if(Len(A2)<2,A2,if(Right(A2,1)<>"Y",A2,"")

column B will either be the value of the value in A, or blank if it has 2 characters and ends with upper-case Y.

You can now filter on column B to exclude the data you don't want.
0
 
HainKurtSr. System AnalystCommented:
try

Select * from Tbl where col NOT Like "*Y"
0
 
cyberkiwiCommented:
> has more that two letter than end in y
If the letter before Y must be a "letter", i.e. A-Z, then

Select * from Tbl where Col NOT Like "*[A-Z]y"
0
 
hnasrCommented:
If your data is:
Y
DY
FY
Hy
EY
AY

What output do you expect?
0
 
wwstudioincAuthor Commented:
hnasr
y
0
 
Jeffrey CoachmanMIS LiasonCommented:
Just :
Y


Or:
Y
Y
Y
Y
Y
Y

?
0
 
Jeffrey CoachmanMIS LiasonCommented:
wwstudioinc,

Did you test any of the solutions above?

They all give an output similar to what you are requesting.

It may just be a matter of pickig the exact output you want.

;-)

Jeff
0
 
cyberkiwiCommented:
I beg to differ..
Apart from my 2 solutions (both of which work for the data provided by hnasr and asker), the other solutions either (1) show everything, or (2) exclude everything.
0
 
nike_golfCommented:
Not Like "*[y]*"

NG,
0
 
HainKurtSr. System AnalystCommented:
looks like access is case insensitive so Y & y are treated same way ;)
so most of the solutions are not valid ;) think again...
0
 
hnasrCommented:
Try this: table types (type)

SELECT types.type
FROM types
WHERE (((Len([type]))<3) AND ((Asc(Right([type],1)))<>Asc("Y")));
0
 
cyberkiwiCommented:
wwstudioinc,

In case you get sidetracked, there were two solutions from above that you have not tested.

Exclude where ends with Y (case insensitive), and the column is at least 2 characters long

     Select * from Tbl where NOT Col Like "*?y"

Exclude where ends with Y (case insensitive), and the column is at least 2 characters long, and Y is preceeded by a letter (i.e. A-Z case insensitive)

     Select * from Tbl where Col NOT Like "*[A-Z]y"
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I beg to differ...>

Sorry, ...I only meant to ask the OP to respond, so that all the posts could be evaluated in order...

The OP only posted once, and it was not clear if they evaluated any of the suggestions...


;-)

Jeff
0
 
cyberkiwiCommented:
Jeff,

No worries and very good suggestion!  Having read the question for the 5th time, I no longer think I understand it right.

wwstudioinc,

In case we have misunderstood you, some of the above solutions may work for certain data, but may not have fully appreciated your exact requirements.

Given this dataset, can you please indicate if
(1) the data is not realistic, will not exist, and can be ignored; and
(2) whether it should be INCLUDED in the final result?
Please also note the mix of upper-lower cases and letters, numbers and punctuations.

John Doe
Y
YY
Xy
xY
y
yy
Johnny
JOHNNY
ABC123Y
SPACE Y
Test.y
<empty string>
<null>
0
 
wwstudioincAuthor Commented:
the expected output should be "Y"  a total of one record
If your data is:
Y
DY
FY
Hy
EY
AY
0
 
cyberkiwiCommented:
Select * from Tbl where Col <> 'Y'
0
 
cyberkiwiCommented:
Sorry, I meant

Select * from Tbl where Col = 'Y'

Based on your answers so far.
0
 
hnasrCommented:
I expect the comments here are  to help you in getting you solution, try to ammend any. Do not expect the exact answer with very little contribution from your side, in analyzing the problem.
0
 
alivarCommented:
Your original question says you want to exclude anything that has more that (than) two letter than (that) end in y. Based on your posts after that it sounds like you mean you want to exclude anything that has 2 or more letters and ends in y.

If you are in Design View of your Query. Try entering this in the Criteria row under your TYPE field of your TYPES table:
Not Like "*?Y"

That should give you what you want
0
 
HainKurtSr. System AnalystCommented:
ok, this is the cure for your problem ;)

Select * from Tbl where Right(col,1) <> Asc('Y')

I want all the points ;)
select asc('Y') as Y, asc('y') as y2, right('HainKurt',1) as lastChar

Y	y2	lastChar
89	121	t

Open in new window

0
 
HainKurtSr. System AnalystCommented:
oops, a fix for above, use this:

Select * from Tbl where Asc(Right(col,1)) <> Asc('Y')
0
 
wwstudioincAuthor Commented:
I have attached a sample to see the problem i am encountering with the db
db1.zip
0
 
cyberkiwiCommented:
Just use this

Criteria for Line:     Not Like "*?Y"

It means, do NOT show rows that have 2 or more characters, and ends with Y
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.