Display only digits on left of decimal maximum three digits in Age Calculation

[wes9659]

Ok I refine this statement a little I am using it as control source in a form. I still need some help tweaking it. Any Ideas!!

=IIf(IsNull([DOB])=False,Date()-[DOB],Null)/365.25

However, I only want it to display the age with out everything after decimal point.
For example, if I entered 06/26/1936 as DOB on form the answer I get is 82.78165639972621492128678987.

How would I round it to show just the 82 in answer? I want to be able to display up to three digits for those over 100 years old.

Thanks everyone for their individual input and taking the time to help me I do appreciate it.

[Micron]

=IIf(IsNull([DOB])=False,Date()-[DOB],Null)/365.25
This means that IF there is a DOB value, return Date()-[DOB] otherwise return Null / 365
You cannot do math on Null, nor can you compare it to anything (e.g. = Null)

I think you want
=IIf(IsNull([DOB]),put value if nulll here, expression if not null here)

EDIT - forgot the main question
I doubt you've run into a record where the field is null otherwise you should have generated an error.
You can use Format function to trim decimals, or you can use a conversion function if you don't want any decimals at all (e.g. CLng)

[wes9659]

I tried =IIf(IsNull([DOB]),put value if null here, expression if not null here) I am not sure what values you want me to put there. Can you give me example please

[Micron]

I have no idea what you want where there is a null value. The field involved seems to be numeric because you're doing calculations, so maybe

=IIf(IsNull([DOB]),0, (Date - [DOB])/365.25)

or to show no decimals (does not remove them from the actual value, just hides them)
=IIf(IsNull([DOB]),0, Format((Date - [DOB])/365.25),0)

or to actually remove them
=IIf(IsNull([DOB]),0, CLng((Date - [DOB])/365.25))

If 0 is not appropriate, pick something else. You could even use null if your table field design allows it.
=IIf(IsNull([DOB]),Null, (Date - [DOB])/365.25)

I don't think you need the () after Date

[Rainlover]

Try breaking your code down into something simple.

e.g IIF([DOB], A )

This should return A if DOB has a value. No need to get involved with NULL. NULL is unique and is not equal to anything. There is nothing much you can do with it.

If DOB returns a value then do your calculation. If not then do nothing.

I hope I am correct as this has not been tested.

Please come back after trying this and you need more help.

[Rainlover]

=IIf(IsNull([DOB]),0, (Date - [DOB])/365.25)

This will divide DOB by 365.25 before it is taken away from DATE.

Not sure if this is what you want.

[Micron]

=IIf(IsNull([DOB]),0, (Date - [DOB])/365.25)

This will divide DOB by 365.25 before it is taken away from DATE.

Not sure if this is what you want.

I think you missed a parenthesis?
(Date - [DOB]) /365

[Rainlover]

I think you missed a parenthesis?
(Date - [DOB]) /365

You are right.

Thanks for that.

[orange]

Further to the advice provided, once you have resolved syntax errors, you could try the Fix function.

[wes9659]

I want to thank everyone who helped me fix my Age Calculation issue in my access form. I thought I would post exactly the steps I did maybe to help someone else with similar issues. Once again thanks everyone!!
This code works perfect to calculate age when used in a form.
Steps:
(1) Create a form
(2) Under View Select Design View
(3) Add a new Text Box
(4) Select Property Sheet Tab on Access top Ribbon
(5) Double Click on Text Box Label Name It Age (You may name it whatever you want)
(6) Than Select unbound box of text box
(7) On Property Sheet selections on right select Data tab
(8) In Control Source select … (three dots on right of Control Source)
(9) In Expression Builder that appears enter the following code
=IIf(IsNull([DOB])=False,Int((Date()-[DOB])/365.25),Null)
**Note: the DOB refers to form source that contains the Date of birth
***Remember you don't need to save Age in a Table because it continuously changes and updates.
(10) Hit OK Close Box
(11) Switch back to Form View and Age should appear in the text box you created

[Rainlover]

wes

Sorry to be the bearer of bad news but your code is wrong. There are not 365/4 days to a year. February changes.

Test it for 4 years on the 1st Day of March. then the 28th and 29th of February.

This should highlight the error.

I do apologise for not reading your Title correctly. Had I had realised you were calculating age I would have been of better help.

I will send you a working copy for age a little later. I have something to do just now.

[Rainlover]

Age.mdb

Try This

Open the form to the full size.

You need to test it because I did not.

[Rainlover]

I assume you are happy with what you have and are not going to look at what I posted.