Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: If between Then Else If statement

    First issue resolved now onto my second.

    I'm trying to have a field autofill with a data from another field. It needs to look into BegTerm & EndTerm fields and if "today's" date is in between one of the BegTerm & EndTerm fields then it needs to autofill with corresponding BaseRent field. I know in SQL it would look something like this...

    Private Sub MonthlyRent_Click()
    if (date() >= [Rent & Option]![BegTermDate1] and Date() <= [Rent & Option]![EndTermDate1]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent1])
    elseif (date() >= [Rent & Option]![BegTermDate2] and Date() <= [Rent & Option]![EndTermDate2]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent2])
    elseif (date() >= [Rent & Option]![BegTermDate3] and Date() <= [Rent & Option]![EndTermDate3]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent3])
    elseif (date() >= [Rent & Option]![BegTermDate4] and Date() <= [Rent & Option]![EndTermDate4]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent4])
    elseif (date() >= [Rent & Option]![BegTermDate5] and Date() <= [Rent & Option]![EndTermDate5]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent5])
    elseif (date() >= [Rent & Option]![BegTermDate6] and Date() <= [Rent & Option]![EndTermDate6]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent6])
    elseif (date() >= [Rent & Option]![BegTermDate7] and Date() <= [Rent & Option]![EndTermDate7]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent7])
    elseif (date() >= [Rent & Option]![BegTermDate8] and Date() <= [Rent & Option]![EndTermDate8]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent8])
    elseif (date() >= [Rent & Option]![BegTermDate9] and Date() <= [Rent & Option]![EndTermDate9]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent9])
    elseif (date() >= [Rent & Option]![BegTermDate10] and Date() <= [Rent & Option]![EndTermDate10]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent10])
    elseif (date() >= [Rent & Option]![BegTermDate11] and Date() <= [Rent & Option]![EndTermDate11]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent11])
    elseif (date() >= [Rent & Option]![BegTermDate12] and Date() <= [Rent & Option]![EndTermDate12]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent12])
    elseif (date() >= [Rent & Option]![BegTermDate13] and Date() <= [Rent & Option]![EndTermDate13]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent13])
    elseif (date() >= [Rent & Option]![BegTermDate14] and Date() <= [Rent & Option]![EndTermDate14]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent14])
    elseif (date() >= [Rent & Option]![BegTermDate15] and Date() <= [Rent & Option]![EndTermDate15]) then
    (Me.MonthlyRent = [Rent & Option]![BaseRent15])
    elseif (date() >= [Rent & Option]![BegOptionYr1] and Date() <= [Rent & Option]![EndOptionYr1]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr1])
    elseif (date() >= [Rent & Option]![BegOptionYr2] and Date() <= [Rent & Option]![EndOptionYr2]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr2])
    elseif (date() >= [Rent & Option]![BegOptionYr3] and Date() <= [Rent & Option]![EndOptionYr3]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr3])
    elseif (date() >= [Rent & Option]![BegOptionYr4] and Date() <= [Rent & Option]![EndOptionYr4]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr4])
    elseif (date() >= [Rent & Option]![BegOptionYr5] and Date() <= [Rent & Option]![EndOptionYr5]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr5])
    elseif (date() >= [Rent & Option]![BegOptionYr6] and Date() <= [Rent & Option]![EndOptionYr6]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr6])
    elseif (date() >= [Rent & Option]![BegOptionYr7] and Date() <= [Rent & Option]![EndOptionYr7]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr7])
    elseif (date() >= [Rent & Option]![BegOptionYr8] and Date() <= [Rent & Option]![EndOptionYr8]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr8])
    elseif (date() >= [Rent & Option]![BegOptionYr9] and Date() <= [Rent & Option]![EndOptionYr9]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr9])
    elseif (date() >= [Rent & Option]![BegOptionYr10] and Date() <= [Rent & Option]![EndOptionYr10]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr10])
    elseif (date() >= [Rent & Option]![BegOptionYr11] and Date() <= [Rent & Option]![EndOptionYr11]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr11])
    elseif (date() >= [Rent & Option]![BegOptionYr12] and Date() <= [Rent & Option]![EndOptionYr12]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr12])
    elseif (date() >= [Rent & Option]![BegOptionYr13] and Date() <= [Rent & Option]![EndOptionYr13]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr13])
    elseif (date() >= [Rent & Option]![BegOptionYr14] and Date() <= [Rent & Option]![EndOptionYr14]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr14])
    elseif (date() >= [Rent & Option]![BegOptionYr15] and Date() <= [Rent & Option]![EndOptionYr15]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr15])
    elseif (date() >= [Rent & Option]![BegOptionYr16] and Date() <= [Rent & Option]![EndOptionYr16]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr16])
    elseif (date() >= [Rent & Option]![BegOptionYr17] and Date() <= [Rent & Option]![EndOptionYr17]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr17])
    elseif (date() >= [Rent & Option]![BegOptionYr18] and Date() <= [Rent & Option]![EndOptionYr18]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr18])
    elseif (date() >= [Rent & Option]![BegOptionYr19] and Date() <= [Rent & Option]![EndOptionYr19]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr19])
    elseif (date() >= [Rent & Option]![BegOptionYr20] and Date() <= [Rent & Option]![EndOptionYr20]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr20])
    elseif (date() >= [Rent & Option]![BegOptionYr21] and Date() <= [Rent & Option]![EndOptionYr21]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr21])
    elseif (date() >= [Rent & Option]![BegOptionYr22] and Date() <= [Rent & Option]![EndOptionYr22]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr22])
    elseif (date() >= [Rent & Option]![BegOptionYr23] and Date() <= [Rent & Option]![EndOptionYr23]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr23])
    elseif (date() >= [Rent & Option]![BegOptionYr24] and Date() <= [Rent & Option]![EndOptionYr24]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr24])
    elseif (date() >= [Rent & Option]![BegOptionYr25] and Date() <= [Rent & Option]![EndOptionYr25]) then
    (Me.MonthlyRent = [Rent & Option]![MonOptionYr25])
    Endif

    End Sub

    Of course this statement would not work in VBA. I just don't know how to do this and any help would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    This should work

    Code:
    Private Sub MonthlyRent_Click()
    
    if date() >= [Rent & Option]![BegTermDate1] and Date() <= [Rent & Option]![EndTermDate1] then 
    Me.MonthlyRent = [Rent & Option]![BaseRent1] 
    Else
    if date() >= [Rent & Option]![BegTermDate2] and Date() <= [Rent & Option]![EndTermDate2] then 
    Me.MonthlyRent = [Rent & Option]![BaseRent2]
    Else
    if date() >= [Rent & Option]![BegTermDate3] and Date() <= [Rent & Option]![EndTermDate3] then 
    Me.MonthlyRent = [Rent & Option]![BaseRent3]
    End If
    End If
    End If
    
    End Sub
    etc...

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    Thanks for the quick response chris07tibgs.

    Do I need to put End If for every Else statement? I'm getting Compile Error: Block If without End If.

    If I go ahead and plug in 40 End If, the above error message goes away but I get Run-Time error '2465' Microsoft Access can't find the field '|1' referred to in your expression.

    Now if I click on "Debug" from error message then "If Date >= [Rent & Option]![BegTermDate1] And Date <= [Rent & Option]![EndTermDate1] Then" is highlighted in yellow.

    Also the parenthesis on "Date()" goes away leaving just "Date" everytime "Date()" is entered.

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Date is ok - that's how vba reads it...I forgot to remove parenthasees in my example.

    Every If needs an End If...not every else. Usually it helps me keep track if I structure it like this

    if ..... then
    .........
    else
    if ..... then
    ......
    else
    if ..... then
    .....
    else
    ......
    end if
    end if
    end if

    For your highlighted error - maybe double check the spelling of the field names you are referring to? Or, if they are fields on a form, not a table or query, it should read [Forms]![myform]![field], or if it is in the same form as the sub, me.field.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Sorry...I tried to space apart my code example above but the forum removed the spaces. I basically have every if statement tabbed one farther out than the last, like an arrow >

  6. #6
    Join Date
    Nov 2011
    Posts
    103
    HAHA! It worked. I didn't know about the end if for all the ifs. Thanks for the info.

    Also yes you were right. I know it's a dumb mistake but the cause of the error was that [Forms] wasn't present in the code. Great suggestion.

    I freakin love this forum.

    Thanks again.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Don't use a serie of ElseIf expressions in such a case. It's slow and almost unreadable. Use a Select Case construction, like this:
    Code:
    Select Case Date
        Case [Rent & Option]![BegTermDate1] To [Rent & Option]![EndTermDate1]
            Me.MonthlyRent = [Rent & Option]![BaseRent1]
        Case [Rent & Option]![BegTermDate2] To [Rent & Option]![EndTermDate2]
            Me.MonthlyRent = [Rent & Option]![BaseRent2]
        Case [Rent & Option]![BegTermDate3] To [Rent & Option]![EndTermDate3]
            Me.MonthlyRent = [Rent & Option]![BaseRent3]
        case ...
    End Select
    2. Moreover there should be no parentheses in an assignment statement:
    Code:
            Me.MonthlyRent = [Rent & Option]![BaseRent1]
    and not:
    Code:
            (Me.MonthlyRent = [Rent & Option]![BaseRent1])
    3. What's "[Rent & Option]"? if it's the name of the form where the code is executed, you should use: "Me!BegTermDate3" or "Forms("Rent & Option").BegTermDate3". The sqare brackets are useless in this case.

    4. You should refrain from using spaces and any non-alphanumeric characters for naming the objects (tables, columns, forms, controls, etc.) in your database: This forces you to use brackets which obfuscates then code, moreover sooner or later you'll be in trouble because of that. The same is true for reserved words.
    Have a nice day!

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why do you have 25 fields in one table that hold the same kind of information? Intentionally denormalized?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2011
    Posts
    103
    Sinndho, I will definitely remember your suggestions and make the changes to my database. I see now that I shouldn't use spaces or any special characters to name my forms, tables, etc... Also the parentheis were added trying to get the statement to work. I was only trying to think of anything and everything that could affect the result. I really do appreciate your input though.

    Teddy, it doesn't contain the SAME information. Similar but not same. The dates in BegTerm and EndTerm are all different and BaseRent are also different. I needed the field to look into all 40 (yes, I know, there 80 total date fields) possible years and grab the corresponding BaseRent. I much rather have the users key them in but it was requested to be automated if at all possible so I wanted to give it a shot.

    Thanks for the input!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •