Trouble with Calculated field.

[jtm013]

Hello,

I am trying to accomplish what I thought would be simple.


I currently have a column in my table which lists the date when a tool is due for calibration. I'm trying to create a calculated field which simply determines if the tool is overdue for calibration. In excel this would be a simple matter of comparing the value in the 'due date' field to the current date. However, I have been unable to find a way to do this in access. I'm sure I am overlooking something simple and any assistance is appreciated.

Thanks,

[June7]

What have you tried?


[due date] = Date()

or

[due date] < Date()

[JoeM]

You can do the same thing in Access. To return the current date in a calculated field, use: Date()

[jtm013]

What have you tried?


[due date] = Date()

or

[due date] < Date()

I have tried both my field name is toolNextCalDate
and I have tried both:

[toolNextCalDate] < Date() and [toolNextCalDate] = Date()

When I try and save the table it returns the error:

"The expression [toolnextCalDate]<Date() cannot be used in a calculated column."

[June7]

Then can't do in table. Do this in query.

[reentry]

If i got u right u have to do such a query : The result should be the days since u should have done the calibration. Means if it should be done yesterday the result would be -1 because u are 1 day to late

SELECT [tableName].toolNextCalDate - date() AS Overtime 
FROM [tableName];

Or the other way around for the positive numbers 1 day delay

SELECT date() -  [tableName].toolNextCalDate  AS overtime
FROM [tableName];

[June7]

No, reentry, don't think that is what they are after.

SELECT * FROM tablename WHERE [due date] < Date();

[reentry]

Sry maybe i got him wrong, your Query is perfekt if he wants to get all machines which are over the time. And my query is calculating how much time is left until they have to calibrate or how many days they are over the time.

[June7]

True, I guess either would serve. Filter criteria could be applied in your query: <=0

[jtm013]

Thanks both for the assistance. However, I already have the capability to run a query to determine if a tool is past its calibration date, using the method described by June7.

However, I need to be able to store a value of some kind which indicates this on-time/overdue status. So that I can then use that value as a datapoint to generate a dynamic pie chart for the user dashboard. All the pie chart is going to display is percent of total tools on-time/overdue.

Thanks for the ongoing assistance.

[June7]

Then use reentry's suggested query to construct a field that calculates a numeric value.

[jtm013]

June 7-

I do not believe this would work for instance if we have a total of 10 tools, 3 are over due. I don't need to know that one of the tools is 2 days late another 1 day, etc. I just need to be able to produce a pie chart indicating that 30% of tools are over due and 70% are on time. The specific number of days will be easily seen once the specific record for the tool is selected.

[June7]

SELECT *, IIf([due date] < Date(), "Late", "NotLate") AS Status FROM tablename;

RowSource for chart:

SELECT Status, Count(*) FROM Query1 GROUP BY Status;