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

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?
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
I thought of using a switch but am not clear about the negative switching concept.  Can you please clarify?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
Is a switch(left([parm],)1)="1" and [parm] not in ("1031","1033"),"1") a good statement?  I'll work on that. . .
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If it works, it's OK :-)
0
 
CFMIFinancial Systems AnalystAuthor Commented:
I'm not understanding the "CHECKFOR1, CHECKFOR2, etc."
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
Dale FyeCommented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
Dale FyeCommented:
Good luck maintaining that.  If anybody ever looks at that query, they are going to ask:

"What ??? is that supposed to do?"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.