?
Solved

Access / SQL query

Posted on 2013-06-14
7
Medium Priority
?
409 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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

765 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