Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access / SQL query

Posted on 2013-06-14
7
Medium Priority
?
412 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
Independent Software Vendors: 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 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 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