Solved

Access / SQL query

Posted on 2013-06-14
7
404 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
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

713 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