Go Premium for a chance to win a PS4. Enter to Win

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
?
154 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 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 49

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 49

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 49

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

916 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