Nested IIF Dilemna

[BikeToWork]

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')))

 

[MajP]

Sorry if I was confusing. You can do something like
where SomeField = getWhere()
If the function returns a value like "10Y" you are OK. But you cannot return an expression like "'OBY' or SomeField = '10Y'" and expect that to result in
where SomeField 'OBY' or SomeField = '10Y'

Your original nested iifs appeared as if you were trying that, your function looked fine.

 

[MajP]

Let me caveat my last. Your function looks conceptually correct, your syntax for a select case is incorrect.
You need an "is" for expression or
a "to" between values. Skip's should work.

 

[BikeToWork]

MajP and others, thank you for your replies. I'm trying to use my function but the query passes a date with no # signs and thus the variable dtEndDate does not match any of the values in the select case. I tried setting the Select Case statement to "Select Case "#" & strEndDate & "#" to match up the hash marks but that does not work either. However, if I plug a date value directly into the function like (Select Case #7/27/2008#)
it works! Here is my current function that I'm using as the criteria value for the YR field in my query:

Function Quotes(dtEndDate As Date) As String

'Select Case #7/27/2008#

Select Case "#" & strEndDate & "#"

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

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

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

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

Case Is < #5/31/2011#
Quotes = "3OY"

Case Is < #5/31/2012#
Quotes = "4OY"

End Select
End Function

Please help and thank you in advance.

 

[SkipVought]

you have declared dtEndDate As Date

The ## delimiters convert a STRING to a DATE.

But you already have a date!!!

Function Quotes(dtEndDate As Date) As String

'Select Case #7/27/2008#
[b]
Select Case dtEndDate
[/b]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BikeToWork]

Skip, this doesn't make any sense but the following function works, despite the fact that the field [End Date] is a date field and dtEndDate is declared as Date datatype. The line dtEndDate = CDate(dtEndDate)made it work. It must be some kind of bug and it sure does not make any sense. Here is the (working) function:


Function OptionYear(dtEndDate As Date) As String

dtEndDate = CDate(dtEndDate)

Select Case dtEndDate

Case Is <= #5/31/2007#
OptionYear = ""

Case Is <= #5/31/2008#
OptionYear = "0BY"

Case Is <= #5/31/2009#
OptionYear = "1OY"

Case Is <= #5/31/2010#
OptionYear = "2OY"

Case Is <= #5/31/2011#
OptionYear = "3OY"

Case Is <= #5/31/2012#
OptionYear = "4OY"

End Select


End Function

 

[SkipVought]

I can run your function like this and it works...

Function OptionYear(dtEndDate As Date) As String

Select Case dtEndDate
Case Is <= #5/31/2007#
    OptionYear = ""
Case Is <= #5/31/2008#
    OptionYear = "0BY"
Case Is <= #5/31/2009#
    OptionYear = "1OY"
Case Is <= #5/31/2010#
    OptionYear = "2OY"
Case Is <= #5/31/2011#
    OptionYear = "3OY"
Case Is <= #5/31/2012#
    OptionYear = "4OY"
End Select
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BikeToWork]

The function will work like that if you pass it a date directly like OptionYear(#7/7/2008#), but not if you pass it the value of [End Date] from the query. The weird thing is that [End Date] is a date field in the table and dtEndDate is a Date datatype in the function. I know it does not make any sense but the function will not work unless I do the dtEndDate=CDate(dtEndDate) line in the code.

 

[Hap007]

So, use what works

Public Function OptionYear(dtEndDate As Date) As String
Select Case CDate(dtEndDate)
Case Is <= #5/31/2007#
OptionYear = ""
Case Is <= #5/31/2008#
OptionYear = "0BY"
Case Is <= #5/31/2009#
OptionYear = "1OY"
Case Is <= #5/31/2010#
OptionYear = "2OY"
Case Is <= #5/31/2011#
OptionYear = "3OY"
Case Is <= #5/31/2012#
OptionYear = "4OY"
End Select
End Function

Access Developer

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