Calculating days difference in a calculated field

Question

I have tried every variation I can think of.

I have a table that displays [Appt Date] in a field. I want to subtract that date from today to give me the [Days to Appt].

I have tried several variations in the expression builder of the calculated field

=DateDiff("d",[Appt Date]-Date())
=[Appt Date]-Date()

And so on.

The error message I am continuously presented with is

The Expression [APPT Date]-Date() cannot be used in a calculated column.

I have tried this in a Date/Time calculated field and a Number Calculated Field

Your help will be gratefully appreciated

Answer 1

Comparing dates seems to be difficult. Developing a calendar by myself I found out, that Microsoft Access 2010 give very different values for apparently equal Dates / times. To compare dates for me is first to calculate the date as a long value.

Dim longTermDate As Long longTermDate = CLng(PubDateDateActual * 10000)

Then the float behaviour of a date or time may be avoided if truncated by cutting off the right portion of the date.

Then I can compare dates. And my calendar app works.

Best regards from Ottobrunn, Bavaria, germany

Answer 2

First, don't use calculated fields. Use a query, that's what they are for.

Second, use the correct syntax for DateDiff:

Select *, DateDiff("d", Date(), [Appt Date]) As Days From YourTable

If [Appt Date] holds date values with no time part, you can even get away with:

Select *, [Appt Date] - Date() As Days From YourTable

If [Appt Date] is not of data type Date, first convert to Date:

Select *, DateDiff("d", Date(), DateValue([Appt Date])) As Days From YourTable
Select *, DateValue([Appt Date]) - Date() As Days From YourTable