Calculating Months Between Two Dates using Access

I am developing a web-based Access dbase and I need to calculate the number of months between the current date (NOW()) and the date of employment input by the user. The calculated value will be stored in the Access dbase table and displayed in a datagrid and as a read-only label in a form. The two fields in the dbase table are "empDate" and "time_in_Service".   I want the calculation to originate and reside in the "time_in_Service" field in the physical dbase, instead of using script on the page to calculate it.

I have tried using the DateDiff function in Access, but the resulting Access message relates that the dbase engine cannot recognize the field name ("empDate") in the function. For example, in the "default value" setting of the "time_in_Service" field, I write the following function;
How do I calculate the months of service as the default value in the "time_in_Service" field based on the current date and a reference to the "empDate" field?
Any Suggestions?
10/9/2003 2:44:54 PM
📃 93655 articles.

💬 2 Replies

If I am understanding your question correctly, you have two fields in the same table, empDate and time_in_service, and you want the default value of time_in_service to be based on the value of empDate.  You can't reference one of the fields in the table in the default value of another field in the same table.  You could accomplish what you want by using the DateDiff function in a query based on your table. Put your DateDiff calc on the field name line of the query design grid (assuming you are working in Access).  You could use the query as the data source instead of the table.

Hope this helps
10/9/2003 5:42:53 PM
Thanks.  That helps alot!!
10/9/2003 6:08:14 PM