Solved

Query Filter - where condition begins with x but doesn't include x and z parameters.

Posted on 2015-01-08
12
136 Views
Last Modified: 2015-01-08
I'm looking for the simplest way to phrase a MS Access query wherein a parameter condition begins with x but doesn't include x and z parameters.  It's getting ridiculously complex and I'm therefore struggling.  I don't have a full array so I'm trying to use some triggers rather than a mapping table.  

For example if the parameter begins with 1 but precludes the parameters 1031 and 1033, then output "1."  Then there are other similar mappings but they get me booged down.

I could write =iif(left([parm],)1)="1",iif([parm] not in ("1031","1033"),"1","2"),iif(left([parm],1)="5",iif([parm] not in ("5234","5444"),"3","5"),"5")).  . . . It starts okay but then I lose track of "ifs" and "thens" and parentheses.

Is there an alternate technique?
0
Comment
Question by:CFMI
  • 5
  • 4
  • 3
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40537985
You could use Choose

=choose(left([param],1),CHECKFOR1,CHECKFOR2,CHECKFOR3 etc) - that will reduce the complexity a little.

However, I would suggest a mapping table if it gets complicated, and maybe use a DCOUNT if necessary.
0
 
LVL 1

Author Comment

by:CFMI
ID: 40537998
I thought of using a switch but am not clear about the negative switching concept.  Can you please clarify?
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40538005
The syntax would be

switch(left([param],1)="1",,CHECKFOR1,left([param],1)="2",CHECKFOR2,left([param],1)="3",CHECKFOR3 etc)

See http://www.techonthenet.com/access/functions/advanced/switch.php

Don't know what you mean by "negative switching concept".
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:CFMI
ID: 40538011
Is a switch(left([parm],)1)="1" and [parm] not in ("1031","1033"),"1") a good statement?  I'll work on that. . .
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40538013
If it works, it's OK :-)
0
 
LVL 1

Author Comment

by:CFMI
ID: 40538014
I'm not understanding the "CHECKFOR1, CHECKFOR2, etc."
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40538034
Personally, I think even the matching table would be difficult to use in this case.  I would probably just write a function and pass it the value of your parameter.

Public Function fnMyFunction(MyParameter as long) as integer

    If Left(cstr(MyParameter), 1) = 1 Then
        If MyParameter = 1031Then
            fnMyFunction = 2
        ElseIf MyParameter = 1033 Then
            fnMyFunction = 2
        Else
            fnMyFunction = 1
        EndIF
    ELSEIF Left(cstr(MyParameter), 1) = 5 Then
        If MyParameter = 5234 Then
            fnMyFunction = 5
        ElseIf MyParameter = 5444 Then
            fnMyFunction = 5
        Else
            fnMyFunction = 3
        EndIF
    ELSE
        fnMyFunction = 5
    EndIF

End Function
 

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40538035
This is your CHECK FOR 1 - so in your above example, that would be iif([parm] not in ("1031","1033"),"1","2")

In other words, what do you want to check for, if the first part of the parm is "1".
0
 
LVL 1

Author Comment

by:CFMI
ID: 40538057
Phillip, You're on the right track.  If the left character is 1 then there are several possibilities, some of which include circumstances in which the parm CANNOT BE some of the possibilities:  1031 or 1033 for example, so if I'm reading you correctly, those are the "CHECKFORs."

Dale, I'm gathering your note might certainly be a good / functional approach.  Given my awkardness with VBA, I was hoping to avoid it.  If I am forced, then I'll probably be back to you with the logistical details.  Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40538110
CFMI,

Time to step up and learn VBA if you are going to use Access much.  It vastly expands your options.  Trying to nest complex logic like you have displayed in the nested iif() statements is extremely difficult to read, and impossible to document.  With the VBA function, you can add remarks to explain your logic, and easily change the logic if something changes in the future (maybe you want to test for "6" but not "6123" or "6124").  With the VBA, it is easy to add this, not so much so with the nested IIF() statement.
0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 40538189
This if,then / switch combination worked!  
            IIf(Left([PARM],1)="1",IIf([PARM] In ("1031","1033"),"2","1"),Switch(Left([PARM],1)="3","3",Left([PARM],1)="4","4")) so if I add to it, I'm good to go.

Thanks for the input.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40538359
Good luck maintaining that.  If anybody ever looks at that query, they are going to ask:

"What ??? is that supposed to do?"
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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