Solved

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

Posted on 2015-01-08
12
138 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
[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
  • 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
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 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 48

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 48

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 48

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

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!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

710 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