Thread: Calculating the interval in days between two dates

1. 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. 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. 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. 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. 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. 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. 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. 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. 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
•