Results 1 to 8 of 8

IIf

  1. #1
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45

    IIf

    A little stummped....

    I have a calculated field in a query which works 100%:

    TotalDays:[EndDate]-[StartDate]

    The only issue is that if the EndDate and StartDate is the same then the result is 0

    I created another field which soves my problem of 0 day results:

    Total_Days: IIf([TotalDays]<="0","1")

    I am having difficulty conbining the two results in the IIf field. What I want to achieve is the following:

    If the calculation in [TotalDays] is correct or returning the correct day count then the TotalDays must show, however if the [TotalDays] is less than or equal to 0 then display 1



    Eg: if [TotalDays] is 3 then it must show 3 but if it is 0 then it must show 1

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    But EndDate -StartDate IS 0 if the dates are the same.
    but math does not use strings: "0"
    IIf([TotalDays]<=0,1,[TotalDays])


  3. #3
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Thanks ranman....i feel like such an idiot

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or perhaps

    TotalDays:[EndDate]-[StartDate]+1

    all depends on what value you want

    a hotel booking with an overnight stay - is that 2 days or 1 day (running from say noon to noon)?

  5. #5
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    It is for rental agreements, so if the customer tented a unit from the 01 February [StartDate] for one day then the [EndDate] is also the 01 February which gives a result of 0 days. That is why I used the IIf to give a result of 1 if the calculation result was 0.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so if they rent a tent today and return tomorrow - that is still one day?

  7. #7
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Yes that is correct

  8. #8
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Try this: Total Days:IIF(enddate-startdate=0,1,enddate-startdate)

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

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