Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Corvallis, Oregon, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating the interval in days between two dates

    How can I calculate the interval in days between two dates (two columns in short date format)?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the interval in days between two dates

    Fred,

    Dates are stored internally as a number of days (since December 30, 1899, to be precise). So you can calculate the number of days between two dates simply by subtracting one from the other. For example in a query:

    Interval: [Date2]-[Date1]

    where Date1 and Date2 are the names of the date fields. Or in the control source of a text box on a form or report:

    =[Date2]-[Date1]

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Location
    Corvallis, Oregon, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the interval in days between two d

    Thanks. I am trying to refer to two fields in a table and calculate that interval. My problem is that I need to create a field in which to do the calculation, but none of the fields in the drop-down list is appropriate. Any one I pick makes the query come up blank.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the interval in days between two d

    You must type the expression

    Interval: [Date2]-[Date1]

    manually, or use the expression builder (Ctrl+F2) to create it.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Corvallis, Oregon, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the interval in days between two d

    My Query has 5 fields from 2 different related tables. The last 2 fields are :"csm_recd_date" and "csm_finaled_date." Each is from the "csm_casemain" table. I have typed the function into the next field under the criteria line, but that requires that I use one of the fields from the two tables in the field line. None of them works (I didn't expect them to). It seems I need to create a new field, but it is just a calculation field. I just can't figure out how to do that.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the interval in days between two d

    You shouldn't enter it in the Criteria line, but in the first empty column in the Field line. Try

    Interval: [csm_casemain].[csm_finaled_date]-[csm_casemain].[csm_recd_date]

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Corvallis, Oregon, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the interval in days between two d

    Now it shows up, but it asks me for a parameter for each of those dates. I don't know what a parameter value is in this case. Thanks for this help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating the interval in days between two d

    If it asks for a parameter, you must have made a typing error in the field name or table name. Check and double check - computers are very finicky, an extra or missing space can cause a name not to be recognized.

  9. #9
    New Lounger
    Join Date
    Oct 2004
    Location
    Corvallis, Oregon, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating the interval in days between two d

    You called it! Typo!

    Thanks so much!!

    Fred

Posting Permissions

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