Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Query excluding

Posted on 2010-08-29
Medium Priority
509 Views
Last Modified: 2012-05-10
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
0
Question by:wwstudioinc
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 10
• 4
• 4
• +4
28 Comments

LVL 13

Expert Comment

ID: 33555001
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

LVL 13

Expert Comment

ID: 33555034
If its Access you can use something like should work.

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

or

Like "?[yY]*"

0

Author Comment

ID: 33555054
Sorry my mistake it should be excluding Y
0

LVL 58

Expert Comment

ID: 33555057
Exclude where ends with Y, and the column is at least 2 characters long

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

LVL 58

Expert Comment

ID: 33555082
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

LVL 59

Expert Comment

ID: 33555090
try

Select * from Tbl where col NOT Like "*Y"
0

LVL 58

Expert Comment

ID: 33555116
> 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

LVL 31

Expert Comment

ID: 33555161
If your data is:
Y
DY
FY
Hy
EY
AY

What output do you expect?
0

Author Comment

ID: 33555174
hnasr
y
0

LVL 74

Expert Comment

ID: 33555188
Just :
Y

Or:
Y
Y
Y
Y
Y
Y

?
0

LVL 74

Expert Comment

ID: 33555216
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

LVL 58

Expert Comment

ID: 33555231
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

LVL 13

Expert Comment

ID: 33555247
Not Like "*[y]*"

NG,
0

LVL 59

Expert Comment

ID: 33555301
looks like access is case insensitive so Y & y are treated same way ;)
so most of the solutions are not valid ;) think again...
0

LVL 31

Expert Comment

ID: 33555302
Try this: table types (type)

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

LVL 58

Expert Comment

ID: 33555311
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

LVL 74

Expert Comment

ID: 33555345
<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

LVL 58

Expert Comment

ID: 33555395
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

Author Comment

ID: 33555557
the expected output should be "Y"  a total of one record
If your data is:
Y
DY
FY
Hy
EY
AY
0

LVL 58

Expert Comment

ID: 33555804
Select * from Tbl where Col <> 'Y'
0

LVL 58

Expert Comment

ID: 33555806
Sorry, I meant

Select * from Tbl where Col = 'Y'

Based on your answers so far.
0

LVL 31

Expert Comment

ID: 33557359
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

Expert Comment

ID: 33562619
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

LVL 59

Expert Comment

ID: 33583312
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
``````
0

LVL 59

Expert Comment

ID: 33583321
oops, a fix for above, use this:

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

Author Comment

ID: 33613814
I have attached a sample to see the problem i am encountering with the db
db1.zip
0

LVL 58

Expert Comment

ID: 33614014
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

LVL 58

Accepted Solution

cyberkiwi earned 2000 total points
ID: 33614029
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month7 days, 12 hours left to enroll

#### 715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.