0

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

Alic
  • 638
  • 6
  • 27
Wolfie84367
  • 102
  • 1
  • 10

2 Answers2

0

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • How would I add that to my table as a field? If queries are there for this purpose. What are calculated fields there for? I can calculate [Appt_date]-7 but I want to calculate the diff in days. – Wolfie84367 Dec 17 '16 at 18:33
  • Calculated fields is some crippled feature aimed at "super users". It won't accept functions - as Date() is - as you have seen. Forget about the feature - it will only cause trouble. – Gustav Dec 18 '16 at 14:53
-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

Helmut Steinecker
  • 1
  • 3
  • Comparing dates is not difficult. _DateDiff_ does that. And always handle dates as dates, not strings, not numbers, no exceptions. Multiplying date values with 10000 makes little sense as one day is 3600 minutes or 86400 seconds. – Gustav Dec 17 '16 at 12:24