Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-08
12
Medium Priority
?
149 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 1000 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
Industry Leaders: 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 earned 1000 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
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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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