Nested IIF Dilemna

BikeToWork · Jun 9, 2011

I have several queries with the below nested IIF criteria. The field Project's 2nd character determines which option year a project is. If it is 4, the option year should be '4OY', if it is 3, the option year should be '30Y', if it is 2, the option year should be '2OY' and if it is 0 the option year could be either '1OY' or '0BY'. I am getting unexpected results from this nested IIF criteria, please help. Is there a better way of doing this like maybe a UDF or something besides a complex nested IIF?

IIf(Mid([tbl_Transactions].[Project],2,1)='4','4OY',IIf(Mid([tbl_Transactions].[Project],2,1)='3','30Y',IIf(Mid([tbl_Transactions].[Project],2,1)='2','2OY','1OY'))) Or IIf(Mid([tbl_Transactions].[Project],2,1)='4','4OY',IIf(Mid([tbl_Transactions].[Project],2,1)='3','30Y',IIf(Mid([tbl_Transactions].[Project],2,1)='2','2OY','0BY')))

SkipVought · Jun 9, 2011

hi,

how about...

Code:

IIf(Mid([tbl_Transactions].[Project],2,1)>='2',Mid([tbl_Transactions].[Project],2,1)&'OY','0BY')

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Hap007 · Jun 9, 2011

Hi

Seems like you do not need 'OR' logic.

IIf(Mid([tbl_Transactions].[Project],2,1)='4','4OY',IIf(Mid([tbl_Transactions].[Project],2,1)='3','30Y',IIf(Mid([tbl_Transactions].[Project],2,1)='2','2OY',IIf(Mid([tbl_Transactions].[Project],2,1)='1','1OY','0BY'))))

Hope This Helps,
Hap...

Access Developer [pc]

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

SkipVought · Jun 9, 2011

if it is 0 the option year could be either '1OY' or '0BY'.

There is not way to program this logic!

You cannot get TWO different results from ONE criteria.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BikeToWork · Jun 9, 2011

I tried

IIf(Mid([tbl_Transactions].[Project],2,1)='4','4OY',IIf(Mid([tbl_Transactions].[Project],2,1)='3','30Y',IIf(Mid([tbl_Transactions].[Project],2,1)='2','2OY',IIf(Mid([tbl_Transactions].[Project],2,1)='0','1OY','0BY'))))

but for some reason the 3OY values aren't showing up now. I also tried the following UDF which does not work right for some reason. The query passes MID([tbl_Transactions].[Project], 2, 1) to the function:

Function OptionYear(strYRChar As String)

Select Case strYRChar

Case "4"
OptionYear = "4OY"

Case "3"
OptionYear = "3OY"

Case "2"
OptionYear = "2OY"

Case "0"
OptionYear = "1OY or 0BY"

Case Else
OptionYear = "Huh?"

End Select

What am I doing wrong here?

randy700 · Jun 9, 2011

Code:

Select Case Mid(Project,2,1
    Case 4: OptionYear = "4OY"
    Case 3: OptionYear = "3OY"
    Case 2: OptionYear = "2OY"
    Case Else: OptionYear = ?
End Select

What is the determining factor when the value tested is not 2, 3, or 4?

Randy

SkipVought · Jun 9, 2011

put a BREAK in your function and OBSERVE what is happening to your variable & logic using the Watch Window, for instance.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Andrzejek · Jun 9, 2011

Looks to me like you ned another table

[tt]tbl_SomeNewTable
YRChar OptionYear
0 1OY or 0BY
1 ????
2 2OY
3 3OY
4 4OY[/tt]

Have fun.

---- Andy

SkipVought · Jun 9, 2011

Where did 1OY or 0BY come from?

Do you actually want the result to be the text, "1OY or 0BY"?

You did not say ANYTHING about that in your original post.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

dhookom · Jun 9, 2011

I'm with Andy. Data belongs in your tables, not in your code

Duane
Hook'D on Access
MS Access MVP

BikeToWork · Jun 9, 2011

Thank you for everyone's advice. I should have explained this before that there are two tables in the query. One of them is tbl_Transactions and it is joined to [ODC Multipliers] on the Activity field. However, the same Activity in the [ODC Multipliers] table can apply to multiple Option Years. The Option year is equivalent to the 2nd character of the Project field from tbl_Transactions (except for when it is 0, which could be either 0BY or 1OY in the [ODC Multipliers] table.

What I'm trying to do with the function is pass the criteria for the YR field in the query. This is a criteria for the field in [ODC Multipliers] but the second character of Project comes from tbl_Transactions. What I need to pass if Mid([Project], 2, 1) is 0 is the string "0BY" Or "1OY", not "0BY OR 1OY". How could I get my function to handle this?

SkipVought · Jun 9, 2011

What I need to pass if Mid([Project], 2, 1) is 0 is the string "0BY" Or "1OY", not "0BY OR 1OY". How could I get my function to handle this?

YOU CANNOT! It makes NO LOGICAL SENSE.

Under what condition would one or the other be returned???

Think about it! If "0BY" and "1OY" indicate the same condition, then why would you need BOTH. Why not just ONE?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BikeToWork · Jun 9, 2011

Skip, I know this sounds illogical, but there are two tables ([tbl_Transactions] and [ODC Multipliers]) in several queries which are joined on Activity. Activity does not determine the Option Year. [ODC Multipliers] has a YR field, which can be either '0BY', '1OY', '2OY', '3OY' or '4OY'. What I am trying to do is set the criteria for the [YR] field in the query so that If the second character of [Project] in [tbl_Transactions] is "0", the value for the field [YR] in [ODC Multipliers] could be either "10Y" or "0BY". If it is "2" then I want the criteria for YR to be "2OY". If it is "3" then the criteria for [YR] should be "3OY". If it is "4" then the criteria for [YR] should be "4OY". For some odd reason, none of the IIF statements (including the ones in earlier posts) work right so I am trying to have a function pass the criteria back to the query. What I've got is:

Function OptionYear(strYRChar As String)
Dim strQuote As String
strQuote = "'"

Select Case strYRChar

Case "4"
OptionYear = "4OY"

Case "3"
OptionYear = "3OY"

Case "2"
OptionYear = "2OY"

Case "0"
OptionYear = strQuote & "0BY" & strQuote & " OR " & strQuote & "1OY" & strQuote

Case Else
OptionYear = "Huh?"

End Select
End Function

The Function returns the string ('0BY' OR '1OY') if 0 is passed to it but for some reason this does not work when I put the function into the Where clause of the query.

SkipVought · Jun 9, 2011

Maybe you need to do something like this..

Code:

Where 
(
    [YourField] = IIf(Mid([tbl_Transactions].[Project],2,1)>='2',Mid([tbl_Transactions].[Project],2,1)&'OY','1OY')
 OR [YourField] = IIf(Mid([tbl_Transactions].[Project],2,1)>='2',Mid([tbl_Transactions].[Project],2,1)&'OY','0BY')
)

So in the instances where the character is >='2' you get

Code:

Where ([YourField] = '2OY' OR [YourField] = '2OY')

and in the instance where it it not, you get

Code:

Where ([YourField] = '1OY' OR [YourField] = '0BY')

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

MajP · Jun 9, 2011

I have to say I am equally confused, but I am guessing here.

As stated this goes in a table not a function.

tbl_SomeNewTable
YRChar OptionYear
0 1OY
0 OBY
1 ????
2 2OY
3 3OY
4 4OY

If I understand under certain conditions (not yet explained) you want the 10Y and other times you want the OBY.

do an inner join where Mid([tbl_Transactions].[Project],2,1) = YrChar

In the case of 0 you will return two records. Now just filter out the one you do not want in your query.
where YrChar <> "OBY"
or
where YrChar <> "10Y"

What determines if I want a 10Y or OBY returned.

BikeToWork · Jun 9, 2011

I don't understand why but the nested IIF is not returning the records it should so I tried to make a function for the criteria of [YR].

There is another field [End Date] in [tbl_Transactions] that also can determine the criteria for the [YR] field in [ODC Multipliers]. It is probably better than the second character of the Project field since there is no ambiguity about 0BY and 1OY. I made a function that parses the [YR] criteria based on the [End Date], however, even though the function passes back the correct value, the query returns no records.

Here is the SQL:

SELECT [ODC Multipliers].YR, tbl_TRANSACTIONS.Activity, tbl_TRANSACTIONS.Project, tbl_TRANSACTIONS.Amount
FROM tbl_TRANSACTIONS INNER JOIN [ODC Multipliers] ON tbl_TRANSACTIONS.Activity = [ODC Multipliers].ODCActivity
WHERE ((([ODC Multipliers].YR)=Quotes([End Date])));

and here is the function:

Function Quotes(dtEndDate As Date) As String
Dim strEndDate As String

Dim strQuote As String

Select Case dtEndDate

Case dtEndDate > #6/1/2007# And dtEndDate < #5/31/2008#
Quotes = "0BY"

Case dtEndDate > #6/1/2008# And dtEndDate < #5/31/2009#
Quotes = "1OY"

Case dtEndDate > #6/1/2009# And dtEndDate < #5/31/2010#
Quotes = "2OY"

Case dtEndDate > #6/1/2010# And dtEndDate < #5/31/2011#
Quotes = "3OY"

Case dtEndDate > #6/1/2011# And dtEndDate < #5/31/2012#
Quotes = "4OY"

End Select

End Function

The function returns the right value but the query comes up with zero rows for some unknown reason.

randy700 · Jun 9, 2011

I really don't see how this can be useful, but....

Code:

IIf(Mid([tbl_Transactions].[Project],2,1)='4','4OY',
IIf(Mid([tbl_Transactions].[Project],2,1)='3','30Y',
IIf(Mid([tbl_Transactions].[Project],2,1)='2','2OY',
IIf(Mid([tbl_Transactions].[Project],2,1]='0','1OY Or 0BY','?'))))

Randy

SkipVought · Jun 9, 2011

Code:

Select Case dtEndDate

Case dtEndDate < #5/31/2007#
    Quotes = ""

Case dtEndDate < #5/31/2008#
    Quotes = "0BY"

Case dtEndDate < #5/31/2009#
    Quotes = "1OY"

Case dtEndDate < #5/31/2010#
    Quotes = "2OY"

Case dtEndDate < #5/31/2011#
    Quotes = "3OY"
    
Case dtEndDate < #5/31/2012#
    Quotes = "4OY"

End Select

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

MajP · Jun 9, 2011

Not sure if this is what you are trying, but certain things can no be done in a query.

You can not dynamically do certain things in a query

1) use a function/calculation to get the top value
Select Top getX() from sometable
select Top Forms!someForm!someControlWithAnumberInIt

2) use a function/calculation to get a table name or field name
select * from getTableName()
select * from Forms!SomeForm!SomeControlWithaTableName
Select Forms!someForm!SomeControlWithFieldName from tblOne
3) Cannot dynmically make a where statement
Where someField = getWhere()

that would work if getWhere returns a value but not an expression like
"OBY" or "1OY

BikeToWork · Jun 9, 2011

I guess I'm back at square 1 since:

"Cannot dynmically make a where statement like Where someField = getWhere()"

is what I was trying to do and it did not work. Now I'm trying the statement as the criteria for the field [YR] in [ODC Multipliers]:

Iif ([tbl_Transactions].[End Date] Between #6/1/11# AND #5/31/12#,'4OY',
Iif ([tbl_Transactions].[End Date] Between #6/1/10# AND #5/31/11#,'30Y',
IIf ([tbl_Transactions].[End Date] Between #6/1/09# AND #5/31/10#,'20Y',
IIf ([tbl_Transactions].[End Date] Between #6/1/08# AND #5/31/09#, '1OY',
IIf ([tbl_Transactions].[End Date] Between #6/1/07# AND #5/31/08#, '0BY')))))

and it is filtering out rows that "should" be in the results. I'm not sure why.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Nested IIF Dilemna

Programmer

Programmer

MIS

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor