Date Calculation

[PTR]

Hi, I hope someone can help me. I am brand new to access and having difficulty with the following:


I have a database with two fields, Date First Absent and Date Returned To Work. I would like to use a query to generate a report that shows the amount of days that the specific employee was absent from work. I am trying to use the DateDiff function to perform this calculation. The result of the calculation must be added to another field in the database called Days Absent. I have set the format of all three fields to Short Date. Any assistance or advice would be greatly appreciated.

Thanks!

[Bob Fitz]

Where are you using the DateDiff() function and do you have it working. IMHO the best place for it would be in the forms query.

[PTR]

Hi Bob, thanks for your help. I am trying to build it into a query. I couldn't manage to get it working though. The error is Data Type Mismatch.

[Bob Fitz]

Can you show us the expression in which you are using DateDiff() or post a copy of the db with a few dummy records

[PTR]

The expression that I am using is DateDiff([Date First Absent],[Date Returned To Work],"d")

[Bob Fitz]

Try:

DateDiff("d",[Date First Absent],[Date Returned To Work])

[PTR]

Thanks Bob, Your code works without any errors but the query is not supplying any results. I must be doing something wrong somewhere. I will shortly post the DB. Could you possibly have a look at it?

[Bob Fitz]

Thanks Bob, Your code works without any errors but the query is not supplying any results. I must be doing something wrong somewhere. I will shortly post the DB. Could you possibly have a look at it?

Sure. No problem

[PTR]

Can't get the DB to attach due to it being 996 Kb

[Bob Fitz]

Can't get the DB to attach due to it being 996 Kb

Try Compact and Repair then create a zip file

[Bob Fitz]

I can't imagine why you couldn't post your original db. It is below with my proposed solution.
You would be wise not to use spaces or special characters like / in the name of any object.

[PTR]

Thanks Bob. I will rename that table without the /
Thanks for your DB, I have extracted it and tried. When I run the query it doesn't provide any results.

Am I missing something?

[Bob Fitz]

The solution is in the form called Absent / RTW.
I have changed the forms Record Source property to a SELECT statement (this could be saved as a query which could then be named in this property) which includes a calculated field called DaysAbsent using the expression:

DaysAbsent: DateDiff("d",[Date First Absent],[Date Returned To Work])

I created a new text box at the top of the forms detail section called DaysAbsent and set it's control soruce property to DaysAbsent.

[PTR]

Sorry Bob, I completely overlooked that. This is exactly what I wanted.
Thanks for your help, I really appreciate it!

[Bob Fitz]

Sorry Bob, I completely overlooked that. This is exactly what I wanted.
Thanks for your help, I really appreciate it!

You're welcome.
Be aware that the field called "Days Away" in your table is not needed.
The calculated field used as the control source of the text box on the form is not and should not be saved to a table. Better to do the calculation whenever and wherever it is required.