Alpha Video Training
Results 1 to 18 of 18

Thread: Count Days In-between Two Date Fields In Report

  1. #1
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Count Days In-between Two Date Fields In Report

    I am trying to figure out how to count the number of days in between two date fields by using a calculated field in a report. I can simply use Date2 - Date1 to get my result but it always 1 day short so I added Date2-Date1 + 1 and it gets me my results but if there is not any dates in the Date1 and Date2 fields then it returns "1" and I want the field to just be null. I am thinking it will be a If/Then statement but cant figure it out for a reports calculated field.

    Any Help Would Be Great.

  2. #2
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,245

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by tommyrotten View Post
    I am trying to figure out how to count the number of days in between two date fields by using a calculated field in a report. I can simply use Date2 - Date1 to get my result but it always 1 day short so I added Date2-Date1 + 1 and it gets me my results but if there is not any dates in the Date1 and Date2 fields then it returns "1" and I want the field to just be null. I am thinking it will be a If/Then statement but cant figure it out for a reports calculated field.

    Any Help Would Be Great.
    Try
    if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,"")
    Last edited by madtowng; 10-30-2010 at 07:51 PM.

  3. #3
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Re: Count Days In-between Two Date Fields In Report

    Thanks but that did not work as well. I loaded that into the expression builder and it would not save, as it said the expression is not valid. Ill do some research on "dtoc" and see if I can get somewhere from there.

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,557

    Default Re: Count Days In-between Two Date Fields In Report

    if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,0)
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by Al Buchholz View Post
    if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,0)
    That worked but it is still returning 1 and not 0 when it evaluates. If i remove the "+ 1" out of the equasion then it evals to "0" as it should. I am thinking that it does not like the "+ 1"

    But thank you for the help. I am going to use it and try to work out the + 1 issue in spare time.

  6. #6
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Count Days In-between Two Date Fields In Report

    dtoc() on an empty date field yields
    Code:
    "  /  /    "
    Try
    Code:
    if(date2<>{} .and. date1<>{},date2 - date1 + 1,0)
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  7. #7
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by Tim Kiebert View Post
    dtoc() on an empty date field yields
    Code:
    "  /  /    "
    Try
    Code:
    if(date2<>{} .and. date1<>{},date2 - date1 + 1,0)
    That did it. Thank you very much :D What is the theory behind using {}. I am trying to learn as I program and would love to know.

  8. #8
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Count Days In-between Two Date Fields In Report

    Your welcome.

    The {} are used to express date values in xbasic the same way quotes are used to express character. So "" means an empty string. And {} means an empty date.

    Code:
    dim d1 as d
    d1 = {01/01/2001}
    
    ?d1
    = {01/01/2001}
    
    d1={}
    ?d1
    = {  /  /    }
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  9. #9
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,245

    Default Re: Count Days In-between Two Date Fields In Report

    if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,"") or if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,0).

    Your choice if you want "" or 0.

  10. #10
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Count Days In-between Two Date Fields In Report

    Tommy
    Just a fundamental question. I'm interested in your reasoning or the application.
    In years of programming in many languages (alpha5 I'm pretty new at, but still supporting legacy systems),
    the date functions return the same. date-date = days between.
    You want never to return one day.

    Number of days between say, midday on the 21st and midday on the 22nd is one day , not two.

    If I did what you ask for a rental agent or hotel they wouldnt have any business.

    Ray

  11. #11
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,557

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by madtowng View Post
    if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,"") or if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,0).

    Your choice if you want "" or 0.
    Not if the field is defined as a numeric.... which is what date2 - date1 + 1 will return.

    Can't have one part of an if return a numeric and the other part a character.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,245

    Default Re: Count Days In-between Two Date Fields In Report

    Now to push a little further.
    In a grid, I want the user to have the option of saying "end date = null".

    I know it won't be that simple, but does anybody know what needs to be entered ? ( I tried <> {})

  13. #13
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by madtowng View Post
    Now to push a little further.
    In a grid, I want the user to have the option of saying "end date = null".
    I know it won't be that simple, but does anybody know what needs to be entered ? ( I tried <> {})
    Hi Gregg (sorry Tommy)
    Off the cuff I would have thought to try " / / "
    so I tried the following

    DIM SHARED date1 as D
    date1=" / / "
    ?date1
    = { / / }

    So it should work.
    Ray
    Last edited by Ray in Capetown; 11-05-2010 at 06:35 AM. Reason: to whom

  14. #14
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Count Days In-between Two Date Fields In Report

    in fact set date1 = "" with nothing (or anything between quotes) makes date1 return { / / }
    I don't mean to be finickiticular but this is useful (to me too)

  15. #15
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,245

    Default Re: Count Days In-between Two Date Fields In Report

    Turns out that for what I was trying to accomplish, is null works correctly if that is the only option, but I still want a way to show my users that they can do a combination (ex: is null or 01/31/2010).

  16. #16
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by Ray in Capetown View Post
    Tommy
    Just a fundamental question. I'm interested in your reasoning or the application.
    In years of programming in many languages (alpha5 I'm pretty new at, but still supporting legacy systems),
    the date functions return the same. date-date = days between.
    You want never to return one day.

    Number of days between say, midday on the 21st and midday on the 22nd is one day , not two.

    If I did what you ask for a rental agent or hotel they wouldnt have any business.

    Ray
    I am building an app that is used for OSHA (Occupational Safety Health Association)injury record keeping. I do recognize that date-date is the number of days in between. I was trying out a few different scenarios and was frustrated because I couldn't make it work and "I KNEW" there was a solution. That is all. That is the type A in me.

  17. #17
    Member tommyrotten's Avatar
    Real Name
    Tommmy Braaten
    Join Date
    Nov 2009
    Location
    In Sunny....NW Washington.
    Posts
    154

    Default Re: Count Days In-between Two Date Fields In Report

    Quote Originally Posted by madtowng View Post
    Turns out that for what I was trying to accomplish, is null works correctly if that is the only option, but I still want a way to show my users that they can do a combination (ex: is null or 01/31/2010).
    Please forgive the non accuracy in my code. I am not on my normal computer to prove it out.

    What if.... You have a check box that the user selects for Null. In the date fields "client side properties" under "calculated field expression" put a "if" statement that evaluates to null if true or the date if false. if(checkbox1=.t.,date1="",date1<>{}) ????

  18. #18
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,245

    Default Re: Count Days In-between Two Date Fields In Report

    I haven't tried the checkboxes yet.
    I guess that could make things easier for the users.

Similar Threads

  1. adding DAYS to Date
    By Leah in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 03-15-2009, 05:23 PM
  2. A Total Count By Days
    By innovative in forum Alpha Five Version 9 - Desktop Applications
    Replies: 7
    Last Post: 02-12-2009, 05:46 PM
  3. ADD 30 DAYS TO DATE FIELD
    By Dennis Poorman in forum Alpha Four Versions 7 and 8
    Replies: 2
    Last Post: 06-10-2005, 04:56 AM
  4. Count business days
    By rmiller3455 in forum Alpha Five Version 4
    Replies: 8
    Last Post: 04-30-2001, 05:52 AM
  5. How to add two business days to a date?
    By tcampb in forum Alpha Four Version 6 and Prior
    Replies: 3
    Last Post: 03-26-2001, 04:52 AM

Bookmarks

Posting Permissions

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