Nested IIf with DateAdd function

Question

Hello All,

I need your assistance in solving my problem in nesting an IIF with DateAdd function in Access.

I have a field txtCurrentRent and below is the code within the control source:

=IIf((DateAdd("yyyy",IIf([LeaseTypeFrame]=2,1),[LeaseStart])-1)<=Date(),[Rent]+([Rent]*[Review%]/100),[Rent])

The type frame has six options starting from 1 to 6 the above is option 2 and works well but how do I nest the rest of other 5 options? For example if (LeaseTypeFrame=1,0), if (LeaseTypeFrame=3,2) and so on.

Thank you in advance.

Answer 1

Hi,

This is what I would use without knowing the full extent of your logic. Make sure you use comments in your code. Also, you can test your function by opening the immediate window (press Ctrl+G) and entering something like:

?GetCurrentRent(2, #1/1/2017#, 1234, 5)

Sorry about the lack of indenting in these replies.

Public Function GetCurrentRent(lngLeaseTypeFrame As Long, datLeaseStart As Date, _

    dblRent As Double, dblReviewPct As Double) As Double

'put some comments here about the logic of your function

' This function is used to get the current rent based on the Lease Type Frame, Lease date, Rent, and Reveiw Percent

' There are 6 possible Lease Type Frames

'  1 -

'  2 -

'  3 -



Dim datToCompare As Date  'create a date variable to break apart expressions



Select Case lngLeaseTypeFrame

    Case 1   'the meaning of 1 goes here like: "short term with renewal clause"

        datToCompare = DateAdd("yyyy", 1, datLeaseStart) - 1

        If datToCompare <= Date Then

            GetCurrentRent = dblRent + dblRent \* dblReviewPct / 100

          Else

            GetCurrentRent = dblRent + dblRent \* dblReviewPct / 100

        End If

    Case 2   'the meaning of 2 goes here

        

    Case 3   'the meaning of 3 goes here

    

    Case 4   'the meaning of 4 goes here

    

    Case 5   'the meaning of 5 goes here

    

    Case 6   'the meaning of 6 goes here

    

    Case Else  'pick up any missed values

        GetCurrentRent = dblRent

End Select

End Function

Answer 2

Hi Dhookom, 

Thanks not as yet.

Possible to assist with me with a complete code for at least two options?

Thanks.

Answer 3

Hi AJK_13,

Has your issue been resolved?

Let us know if you need more assistance or clarification.

Answer 4

Another option is to use the After Update event of the Option Frame to populate the other control.

So I would use a SELECT CASE

Select Case Me.LeaseTypeFrame

Case 1

Me.txtCurrentRent = Expression

etc.

End Select

Answer 5

I would not nest IIf()s like this. It's way to messy and difficult to maintain.

This expression should be encapsulated in a user defined function.

Public Function GetCurrentRent( lngLeaseTypeFrame as Long, _

       datLeastStart as Date, dblRent as Double, dblReviewPct as Double) as Double

' your code with SELECT CASE goes here

GetCurrentRent = ...

End Function