Results 1 to 9 of 9

20050907, 22:43 #1
 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)?

20050907, 22:54 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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]

20050908, 14:52 #3
 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 dropdown list is appropriate. Any one I pick makes the query come up blank.

20050908, 14:57 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050908, 19:09 #5
 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.

20050908, 19:16 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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]

20050908, 19:56 #7
 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.

20050908, 20:06 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20050908, 20:30 #9
 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