Help with Access Formula

[tdawn]

Hey guys, I need help creating a formula. I'm really new to this and can use all the help I can get.



I am trying to create a formula and I will do my best to describe what I need.

If [date of hire] is < or = to 1 year from today's date
then populate [new hire] with a zero.

If [date of hire] is > 1 year from today's date and [years since last evaluation] is null (empty)
then populate [years since last evaluation] with difference of today's date [date of hire]

I also need the [years since last evaluation] to be in decimal format. Like if it's a year and 3 months then I need it to say 1.4.

I really appreciate any help you guys can give me!! Thank you all in advance!!

Also, does anyone happen to know a good source of info when I can start learning how to create basic formulas?

[June7]

Requires VBA code to save the calculated value.

Ideally, don't populate fields with calculated results, just do calcs when needed. A basic principle of relational database is save raw data, do calcs on reports. Problem with storing calculated results is the calc can get 'out-of-sync' with the raw data. If there is an error updating the calculated value, fixing the result can be difficult, especially with cumulative data like in financial accounting. Your data is not cumulative so maybe not so critical but still requires code to save calc result.

To just calc 'on the fly' in query or in textbox ControlSource on form or report:

IIf([date of hire]<= DateAdd("y",1,Date()),0,-1)

Do you not have a separate table that stores the evaluation data, including date of evaluation?

Google: Access expressions
http://office.microsoft.com/en-us/ac...010096295.aspx

[tdawn]

Thank you so much for your help. I hope in my response I do not sound like a complete beginner ( I am). The table that I am working with has quite a few fields. I don't have it in front of me but I do know there is field for [date of hire] , [years since last evaluation], [new hire]. If the date of hire is less than 1 year from todays date then it should put a zero in the [new hire field]. If they have not had an eval and they are not new then the difference should be put in the [years since last evaluation] field.

I really appreciate your advice about not populating fields with calcs but with this one I'll do it in the access table.

The formula you provided did not mention anything about putting the answer of the calculation in the [years since last evaluation] field. Do you have anything insight on this??

[June7]

I did mention requires VBA code to save calculated value.

With a form open to the record, code in some event can save the results to fields. The real trick is figuring out what event to put code in.

Me![years since last evaluation] = Me.textboxname

However, the calcs you need can be done on-the-fly whenever needed.

Calcs in query:

SELECT *, IIf([date of hire]<= DateAdd("y",1,Date()),0,-1) AS IsNewHire, IIf([IsNewHire], Null, DateDiff("M",[date of hire],Date())/12 AS [years since last evaluation] FROM tablename;

Do some experimenting with the DateDiff and DateAdd functions. You will find that the DateDiff calc I show doesn't include partial current month. DateDiff rounds to the nearest whole unit.

[tdawn]

June7, thank you so much... I can't wait to get to work on Monday and try this out. I'll let you know how it goes Thank you again for your time!!

[June7]

Review this link which has a good discussion on calculating dates https://www.accessforums.net/forms/d...ton-33572.html