Solved

Access / SQL query

Posted on 2013-06-14
7
406 Views
Last Modified: 2013-06-14
A table in the database contains a field "IDNumber" that is a text fields.

I need to write a query that will allow me to filter the "IDNumber" by any of the following:
Starts with
Ends with
Contains
Does not start with
Does not contain
Equals

Is it possible to do this in an Access query?
0
Comment
Question by:rrhandle8
[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
7 Comments
 
LVL 11

Expert Comment

by:Guru Ji
ID: 39248471
u can do the like statement against that column

SELECT *
FROM TableName
WHERE IDNumber LIKE '*FilterValue*'
0
 

Author Comment

by:rrhandle8
ID: 39248535
Yes, I know how to write standard queries.
I am looking for a solution--and I don't think it is possible--to have a box popup when the query runs, and the user can filter by any of the conditions I listed.
0
 
LVL 40

Expert Comment

by:als315
ID: 39248662
You can do it in query with a lot of IIFs or with function
Function could be like this:
Public Function FilterS(A As String, B As String, N As Long) As Boolean
FilterS = False
Select Case N
Case 1   'Starts with
    If A Like (B & "*") Then FilterS = True
Case 2   'Ends with
    If A Like ("*" & B) Then FilterS = True
Case 3   'Contains
    If InStr(1, A, B) > 0 Then FilterS = True
Case 4   'Does not start with
    If InStr(1, A, B) = 1 Then FilterS = True
Case 5   'Does Not contain
    If InStr(1, A, B) = 0 Then FilterS = True
Case 6   'EqualsCase
    If A Like B Then FilterS = True
End Select
End Function

Open in new window

DBIF.accdb
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:als315
ID: 39248724
Sample with IIF and function
DBIF.accdb
0
 

Author Comment

by:rrhandle8
ID: 39248830
Thanks for the suggestions, but it looks like you are using VBA.  I know how to do that.  Here is an example:

SELECT Episode.IDNumber, Episode.EpisodeID, Episode.PatientID
FROM Episode
WHERE .........................

What do I put in the .................. area with out creating any functions in VBA?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39249065
Starts with
Ends with
Contains
Does not start with
Does not contain
Equals

Assuming you have a form, in which your user selects the "compare type" (Starts with, ends with, etc) and then enters a value to match...


SELECT IDNumber, EpisodeID, PatientID
FROM Episode
WHERE Switch(
    Forms![FormName]![CompareType] = "Starts with" And IDNumber Like Forms![FormName]![CompareValue] & "*", True,
    Forms![FormName]![CompareType] = "Ends with" And IDNumber Like "*" & Forms![FormName]![CompareValue], True,
    Forms![FormName]![CompareType] = "Contains" And IDNumber Like "*" & Forms![FormName]![CompareValue] & "*", True,
    Forms![FormName]![CompareType] = "Does not start with" And Not IDNumber Like Forms![FormName]![CompareValue] & "*", True,
    Forms![FormName]![CompareType] = "Does not end with" And Not IDNumber Like "*" & Forms![FormName]![CompareValue], True,
    Forms![FormName]![CompareType] = "Does not contain" And Not IDNumber Like "*" & Forms![FormName]![CompareValue] & "*", True,
    Forms![FormName]![CompareType] = "Equals" And IDNumber = Forms![FormName]![CompareValue], True,
    True, False)

Open in new window


For more about Switch:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0
 

Author Closing Comment

by:rrhandle8
ID: 39249166
matthewspatrick, you almost nailed it.  With all the years I have behind me writing queries I did not think this was possible, but using your code as a starting point, I changed it to allow the user to type in what type of compare they want to do.

This is a real "gold nugget."  Thank you.

SELECT Episode.IDNumber, Episode.EpisodeID, Episode.PatientID
FROM Episode
WHERE Switch(
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Starts with" And IDNumber Like [CompareValue] & "*", True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Ends with" And IDNumber Like "*" & [CompareValue], True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Contains" And IDNumber Like "*" & [CompareValue] & "*", True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Does not start with" And Not IDNumber Like [CompareValue] & "*", True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Does not end with" And Not IDNumber Like "*" & [CompareValue], True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Does not contain" And Not IDNumber Like "*" & [CompareValue] & "*", True,
    [Starts With, Ends With, Contains, Does not start with, Does not end with, Does not contain, Equals] = "Equals" And IDNumber = [CompareValue], True,
    True, False)
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

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

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

Join & Ask a Question