Solved

Access / SQL query

Posted on 2013-06-14
7
399 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 39

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 39

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This collection of functions covers all the normal rounding methods of just about any numeric value.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now