Date calcualation

[Scott]

I have three fields: Start Date, End Date & Duration. For example, Start
Date is 12/10/07, End Date is 12/10/07 and the Duration is a calculation
field and the result is 0. How can I make it 1?

Thanks,

Scott

 

[John W. Vinson]

I have three fields: Start Date, End Date & Duration. For example, Start
Date is 12/10/07, End Date is 12/10/07 and the Duration is a calculation
field and the result is 0. How can I make it 1?

What result would you want if End Date is 12/11/07? Also 1? If so:

IIF([Start Date] = [End Date], 1, DateDiff("d", [Start Date], [End Date])

John W. Vinson [MVP]

 

[Scott]

John,

Thanks for your formula. For 13 October, I want the result of 2.

Scott

I have three fields: Start Date, End Date & Duration. For example, Start
Date is 12/10/07, End Date is 12/10/07 and the Duration is a calculation
field and the result is 0. How can I make it 1?

What result would you want if End Date is 12/11/07? Also 1? If so:

IIF([Start Date] = [End Date], 1, DateDiff("d", [Start Date], [End Date])

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Thanks for your formula. For 13 October, I want the result of 2.

Scott

I have three fields: Start Date, End Date & Duration. For example, Start
Date is 12/10/07, End Date is 12/10/07 and the Duration is a calculation
field and the result is 0. How can I make it 1?

What result would you want if End Date is 12/11/07? Also 1? If so:

IIF([Start Date] = [End Date], 1, DateDiff("d", [Start Date], [End Date])

John W. Vinson [MVP]

I'm not sure I understand. Note that Access literal ##/##/#### dates in
Queries are always interpreted as American format, month-day-year - regardless
of your computer's date preferences!

DateDiff("d", Startdate, Enddate) will return the number of midnights between
Startdate and Enddate. You can add one to that result if you want to count the
start date as a full day.

John W. Vinson [MVP]