IIF with multiple Null values

[touchmove]

Hi everyone!

I am currently designing applicants access database and below details troubled me so far:

A	B	C	D
ID	FROM	TO	EXP
1	10/1/2014	11/1/2015
2	10/2014
3			2.75

<tbody>
</tbody>

I want to add a calculated field by using access query which would result the years of experience:

Data above is based on different resume sent to me. Some resume just have years of experience without specific dates that's why I create another field for such data (column D)

Now, here's what I want regarding null values:

ID 1: Column C - Column B (using IIF Null DateDiff)
ID 2: "present" (as the applicant is currently working)
ID 3: 2.75 (just get the value if Column B and Column C is null

I had made good result with ID 1 and 3 but fails with ID 2.


Expr1: IIf([From] Is Null,[Years],IIf([Years] Is Null,DateDiff("yyyy",[From],[To])))



Your help will be very much appreciated:

 

[touchmove]

After several trial and error attempts using this code:

Expr1: IIf([To] Is Null And [Years] Is Null,"present",IIf([From] Is Null,[Years],IIf([Years] Is Null,DateDiff("m",[From],[To])/12)))


The result:

ID	From	To	Years	Expr1
1	10/1/2014	11/1/2015		1.08333333333333
2	10/1/2014			present
3			2.75	2.75

<caption> experience Query </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

Question: Can I make the number result into 2 decimal places whereas there is text value (present) in the field?

 

[Joe4]

You can use the ROUND function to round your calculation to two decimal places.

By the way, I deleted your duplicate post. Please do not post the same question multiple times. Per forum rules, duplicate posts will be locked or deleted.

Thanks

 

[touchmove]

Do I need to make another calculated field or insert round off function to the existing IIF function (which is so frustrating to beginner like me)?

Thanks for the reply.

 

[Joe4]

Just insert it in your formula, the specific calculation part, i.e.

..., ROUND(calculation formula,2), ...