Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2017, 05:36 AM   #1
livmun
Newly Registered User
 
Join Date: Jun 2015
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
livmun is on a distinguished road
IIF AND Multiple

Hi, I'm attempting to categorise my data using nested IIf AND functions in query design but it's not working. Can anyone help?

Priority: IIf([ColumnA] = "*abc*","P1",IIf(([ColumnA]="*def*") And ([ColumnB] Is Not Null),"P2"),IIf(([ColumnA]="*def*") And ([ColumnB] is null, P3")

livmun is offline   Reply With Quote
Old 07-11-2017, 05:56 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,392
Thanks: 0
Thanked 753 Times in 736 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: IIF AND Multiple

rather than nest IIFs which is complex and easily to error,
Either:
make nested queries (UNION query) to get the results
or
make a custom formula to parse the field into the correct buckets.

Code:
function getCode(pvColA, pvColB)
select case true
    case instr(pvColA,"abc")>0 
          getCode = "P1"
    case instr(pvColA,"def")>0 and not IsNull(pvColB)
          getCode = "P2"
    case instr(pvColA,"def")>0 and IsNull(pvColB)
          getCode = "P3"
end select
end function
Ranman256 is offline   Reply With Quote
Old 07-11-2017, 06:14 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,387
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: IIF AND Multiple

'it's not working' isn't very helpful

but as a guess

IIf([ColumnA] = "*abc*","....

you are not looking for a value of '*abc*' but something that contains 'abc'

if you are then you should use Like

IIf([ColumnA] Like "*abc*","....

Only use = when looking for a specific value, in which case it would be

IIf([ColumnA] = "abc","....

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-11-2017, 06:40 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,490
Thanks: 12
Thanked 2,303 Times in 2,254 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: IIF AND Multiple

1. When you get that nested its time for a custom function.

2. Your logic isn't complete. Meaning, it's possible for Priority to be NULL.

My advice is to write out what you hope to accomplish in English, then convert that to code:


If A contains 'abc' its 'P1'
If A contains 'def' and B has a value it's 'P2'
If A contains 'def' and B doesn't have a value its 'P3'
If no value assigned up until now its 'Error'

plog is offline   Reply With Quote
Reply

Tags
iif , multiple criteria , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import multiple fix width txt files and eliminate multiple headers and footers NewGuy23 Modules & VBA 3 08-03-2013 09:49 AM
display Multiple columns pass from the multiple selection listbox to report ice051505 Modules & VBA 4 03-22-2013 01:42 PM
Question Export access table to multiple excel workbooks with multiple tabs captdkl02 General 7 12-14-2012 12:05 PM
Importing multiple uneven sized ranges from .csv file into multiple tables NZArchie Queries 12 05-25-2011 01:03 AM
e-mailing multiple reports to multiple recipients based on 1 query wilkob Reports 0 01-10-2005 07:19 AM




All times are GMT -8. The time now is 06:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World