DateDiff

[ddrew]

Im using an unbound textbox (using this as I read that its wrong to store calculated data!) to look at the Datediff between a Dateof Birth and Now(). This is my code:

=DateDiff([Year],[DateOfBirth],Date())

The problem is that it dosen't work, it just says name in the text box

 

[pbaldy]

The first argument is a string. if you check in help, you should see the appropriate syntax.

 

[ddrew]

Excuse my ignorance, but I dont understand StringsPlease explain?

 

[spikepl]

Paul is suggesting a revolutionary approach, that works surprisingly often: look up the function in the documentation.

 

[Brianwarnock]

Paul is suggesting a revolutionary approach, that works surprisingly often: look up the function in the documentation.

LOL, you beat me to the response so I have edited mine, amazing how many posters refuse to use Access help when it is suggested.

Brian

 

[ddrew]

Thanks for the help! I did look it up but didnt understand hence my asking!!!!! If I had understood I wouldn't have asked!!!! Prior to coming on the Forum I always make a point of trying to work it out for myself via the help menus and then searching the Forum. If I'm not getting the help, then little point in responding is there!!!!!

I know people give there time for free, but I can do without the sarcastic remarks, thanks!

 

[jdraw]

try google

http://www.techonthenet.com/access/functions/date/datediff.php

 

[missinglinq]

As has been suggested, you really do need to learn the basics when starting out, and Access' Help is a good, free starting point.

Calculating an accurate age is a bit more complicated than simply using the year of birth and current year, because we each have two ages in a given year, the age before we celebrate our birthday and the age on and after this celebration! This formula calculates the accurate age using DateDiff().

AgeToday = DateDiff("yyyy", [DOB], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOB], "mmdd"), 1, 0)

Linq ;0)>

 

[Brianwarnock]

I fail to see how you came up with the code you did if you read help, you may have just put yyyy instead of "yyyy" but not [year] .

One question , do you want to just subtract the years 2011- 1999 to give 12 or do you want to take note of the actual birthdate etc including month and day

Brian

 

[ddrew]

Brian, I didnt write the word year, I wrote YYYY, so I cant answer the question. What I now know is that all I was missing was a set of quotes around the yyyy so should have read "YYYY".

 

[Brianwarnock]

Hmm, so Access changed yyyy to [year], how odd. What about my question, are you really looking at adage calculation or just a difference in years, they are different.

Brian

 

[ddrew]

Hmm, so Access changed yyyy to [year], how odd. What about my question, are you really looking at adage calculation or just a difference in years, they are different.

Brian

All I wanted was an age in whole years, so it was Date of Birth to Now.

With regards to access changing it, I'm using office 2010 and when you go to Control Source and write =DateDiff(y you get a popup so I assumed, rightly or wrongly that you choose the option! Ive attached a screen grab for you.

 

[pbaldy]

Boy, a lot happened while I slept! Note in your picture that there is only one "f" in the function. Access is not helping with the DateDiff() function at that point, it's assuming you're starting the Year() function.

 

[ddrew]

Boy, a lot happened while I slept! Note in your picture that there is only one "f" in the function. Access is not helping with the DateDiff() function at that point, it's assuming you're starting the Year() function.

Typo as I was recreating it, but you do get the same even if you put both f's in!

 

[Brianwarnock]

As it is an age calculation you need to allow for whether the actual birthday has been reached or not , there are a number of ways to do this but read this

http://office.microsoft.com/en-us/a...-from-a-birth-date-in-access-HA001055071.aspx

Brian

 

[Mayanja_N]

As has been suggested, you really do need to learn the basics when starting out, and Access' Help is a good, free starting point.

Calculating an accurate age is a bit more complicated than simply using the year of birth and current year, because we each have two ages in a given year, the age before we celebrate our birthday and the age on and after this celebration! This formula calculates the accurate age using DateDiff().

AgeToday = DateDiff("yyyy", [DOB], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOB], "mmdd"), 1, 0)

Linq ;0)>

I am just too new to coding access, but where excatly do I insert the code to calculate the age if I have DOB column and a new and empty column of age
Thanks

 

[missinglinq]

I'm guessing, from your use of the term 'column,' which is not really an Access term, that your Form is in Datasheet View, i.e. many Records are visible at the same time, rather than a Single View Form, with one Record at a time displayed. This being true you have to use the Control Source Property to do this.

In Form Design View, Right-Click the Unbound Textbox, then Click on Properties - Data and in the Control Source Property box enter

= DateDiff("yyyy", [DOB], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOB], "mmdd"), 1, 0)

Linq ;0)>

 

[rodmc]

Yeah, Ive used datediff in the past, and it wasnt always what I expected it to be.

If you are looking to get an Age (e.g snapshot at a specific date) then datediff will not give you what you need I learned this to my cost and it basically meant having to start from scratch on the project (statistical returns) I was on.

 

[ypma]

I am using 2003 and the this coding works for me,

=age([DOB]) & "yrs" & " " & AgeMonths([DOB]) & "months"

 

[rodmc]

I generally just use

=Round((Date()-[DoB])/365)

in the control source property

works for me, although I do not need months and such, just the age in yrs