Results 1 to 14 of 14
  1. #1
    Chris_Cline is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7

    IIF Statement Syntax for Multiple Conditions

    Newby here, Having an issue trying to get the correct syntax on this statement with multiple conditions. Below is the code and the results. The dates are being calculated correctly. My intent was for the period end date field for one-time payments to read "One Time Payment". Instead I Get #ERROR, I have tried different arrangements of where to place the final condition to no avail. Any help would be appreciated.


    PeriodEndDate:
    IIf([Lease].[BILLING FREQUENCY]="MONTHLY",(DateAdd("m",1,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",(DateAdd("q",1,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",(DateAdd("m",6,[EFF DATE])-1),


    IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),"One Time Payment"))))

    BILLING FREQUENCY PeriodEndDate
    MONTHLY 2/28/2021
    ANNUAL 1/31/2022
    MONTHLY 2/28/2021
    ONE-TIME
    ANNUAL 2/6/2022
    QUARTERLY 6/30/2021
    SEMI-ANNUAL 7/31/2021
    ANNUAL 2/11/2022
    MONTHLY 2/28/2021
    ANNUAL 2/3/2022
    ANNUAL 2/11/2022
    ANNUAL 2/22/2022
    ANNUAL 2/26/2022
    ANNUAL 1/31/2022
    ANNUAL 2/26/2022
    MONTHLY 2/28/2021

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put all this on the form. User picks the Billing Frequency.
    It set the End Date textbox.
    the query does the rest:
    Attached Thumbnails Attached Thumbnails date set.png  

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First thing that comes to mind is that there is at least 1 record where frequency is ANNUAL but [EFF Date] is Null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It might be that you are trying to force a string into a date field. Can you try to wrap the DateAdds in Format()which will convert them to text as well:

    IIf([Lease].[BILLING FREQUENCY]="MONTHLY",Format((DateAdd("m",1,[EFF DATE])-1),"MM/DD/YYYY"),
    IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",Format((DateAdd("q",1,[EFF DATE])-1),"MM/DD/YYYY"),
    IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",Format((DateAdd("m",6,[EFF DATE])-1),"MM/DD/YYYY"),
    IIf([Lease].[BILLING FREQUENCY]="ANNUAL",Format((DateAdd("yyyy",1,[EFF DATE])-1),"MM/DD/YYYY"),"One Time Payment"))))

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Format function returns a string, so all formatted results would be strings? Yet only the last one fails even though all results go into the same field.
    Last edited by Micron; 01-08-2021 at 12:47 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try to wrap the DateAdds in Format()which will convert them to text as well:
    I should have said "string"
    I was trying to convert all to a uniform data type; maybe leave it the way it was and use #12/31/1900# instead of "One Time Payment" and see what happens.
    @micron not sure I got your first suggestion right but if an ANNUAL would fail because of a Null date how would it show as ONE TIME #ERROR.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I am really confused as to why anyone would try and put a string into a date field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Chris_Cline is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    Thanks,
    This worked. Curious as to how else this could have been written to make this work:
    IIf([Lease].[BILLING FREQUENCY]="MONTHLY",(DateAdd("m",1,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="QUARTERLY",(DateAdd("q",1,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="SEMI-ANNUAL",(DateAdd("m",6,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),
    IIf([Lease].[BILLING FREQUENCY]="ONE-TIME","One Time Payment","other")))))

  9. #9
    Chris_Cline is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    7
    It is intended to go into an invoice for rentals on RR property

    Typically rent payments are for a certain period of time whether monthly or other
    Sometimes there are lump sum payments that cover a perpetual occupancy in this case I want the field that shows the period end date to state "One Time Payment" as there is no end date.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I used to get the #error in a form bound to a query in which I was calculating the age of a student then binging the textbox on the form to the calculated field in the query. I was able to solve it by simply moving the same calculation to the control source property of the rextbox on the form (so not having the control bould to the query field but doing the calculation in the form for the current record).

    I would be curious to see what happens if you attempt the same (I assume your invoice is a report), use your initial formula in the control source of the textbox that displays the end date.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure what you mean by "how would it show as ONE TIME #ERROR."
    The reported error is "#ERROR" ?

    IMO it would only take one record where eff date is null where the frequency value is "ANNUAL" and the result would be #ERROR. Perhaps it is something else, but how difficult is it to look at the table field or create a test query to look for Null in said field?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    My intent was for the period end date field for one-time payments to read "One Time Payment". Instead I Get #ERROR, I have tried different arrangements of where to place the final condition to no avail.
    @micron I think the OP said that the #ERROR shows up for the "One Time" frequency record(s) , not for "Annual" one(s). And in his example only the One Time record had an empty date, so I don't know how you get to Annual as you suggested in your post.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIf([Lease].[BILLING FREQUENCY]="ANNUAL",(DateAdd("yyyy",1,[EFF DATE])-1),"One Time Payment"))))
    My take was:
    Table is Lease; field is [BILLING FREQUENCY] value being tested is "ANNUAL"; choice is to be either [EFF DATE] or "One Time Payment" based on value.
    There are no "One Time Frequency" values in the records. I saw the posted data as a result not the source data, and where the frequency output is "One Time" [EFF DATE] (if that's what PeriodEndDate represents) the date is missing, hence I suggested the Null issue. You and I are looking at things differently and I guess I missed the fact that a solution was accepted so I'm done here.
    Last edited by Micron; 01-08-2021 at 08:26 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Shouldn't have spaces in object names.
    ([BILLING FREQUENCY] --> Better would be BillingFrequency
    [EFF DATE] --> better would be EffDate

    Use Camel Case instead of all Caps

    Post #8
    Quote Originally Posted by Chris_Cline View Post
    <snip> Curious as to how else this could have been written to make this work:
    Consider
    Code:
    SELECT CASE [Lease].[BILLING FREQUENCY]
        Case "MONTHLY"
            PeriodEndDate = DateAdd("m",1,[EFF DATE])-1
        Case "QUARTERLY"
            PeriodEndDate = DateAdd("q",1,[EFF DATE])-1
        Case "SEMI-ANNUAL"
            PeriodEndDate = DateAdd("m",6,[EFF DATE])-1
        Case "ANNUAL" 
            PeriodEndDate = DateAdd("yyyy",1,[EFF DATE])-1
        Case "ONE-TIME"
            PeriodEndDate = "One Time Payment"
        Case Else
            PeriodEndDate = "Other"
    End Select

    Post #9
    Quote Originally Posted by Chris_Cline View Post
    It is intended to go into an invoice for rentals on RR property
    So somewhere you have a RenterID? Would like to see the rest of the code.
    Normally do not use [Lease].[BILLING FREQUENCY] nomenclature. (at least I don't.)


    But...you seem to have what you want...... good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 09-08-2017, 01:23 PM
  2. Help Statement meeting 2 conditions
    By Holli in forum Programming
    Replies: 5
    Last Post: 05-08-2015, 07:17 AM
  3. iff statement with a few conditions
    By Eric2013 in forum Queries
    Replies: 2
    Last Post: 09-15-2014, 06:12 PM
  4. Replies: 14
    Last Post: 02-08-2012, 03:36 PM
  5. iif statement with multiple conditions
    By ragsgold in forum Queries
    Replies: 7
    Last Post: 08-24-2011, 05:38 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums