Birthdate Expression in query

[southboy]

Hey Everyone!
I have a birthdate database for work and had to convert it to 2003 access. It tells me how old a person is based on this query expression -
Age: DateDiff("d",[birthdate],Now())+1
This worked great in Access 97 but in 2003 now anyone born after 07/01/1977 it comes up as a year instead of age starting with 1929 if they were born in 1977. Anyone born before 07/01/1977 has an age, but with a birthdate after 07/01/1977 their age is reported as a year.

Here is the query result:
Fullname Age BirthDate
Harper, Marvin H 44 11/20/1962
Tolodeo, Aaron 1929 07/18/1977
Crowford, Susan 1921 08/23/1985

Has anyone else come across this?
Any suggestions?

 

[SkipVought]

d = #7/18/1977#
MsgBox DateDiff("yyyy", d, Now)

I get 30.

Check your HELP on DateDiff

Skip,

_[red][/red]

 

[southboy]

Skipvought Thanx for your quick reply. I only request help to Tek Tips as a last resort and only after checking the MS online help, MS Access application help and Tek Tips for my problem. I have tried all sorts of similar suggested coding from all other area's I could think of. Some gave EVERYONE a year instead of their age and others gave me the same response that I already get.

What I don't get is why the expression works for those dates before 07/01/1977 and not for the ones after 07/01/1977.

This is an expression in a query that is feed into a combo box form. Can you explain how your suggested coding can help me here?
Thanx.

 

[SkipVought]

The expression you were using returns DAYS.

Skip,

_[red][/red]

 

[southboy]

SkipVought that is the code that worked in Access97 for years and works somewhat in 2003. If I use
Age: DateDiff("yyyy",[Birthdate],Now())
Then it returns the year 1900 for everone in the query regardless of them being born before or after 07/01/1977.

query result using 'yyyy' in the expression:
Fullname Age BirthDate
Adkins, Ronald 1900 8/11/1949
Albright, Kerry, 1900 4/12/1961
Alexander, Mike 1900 9/6/1956
Allen, Brenda 1900 6/27/1961
Allen, Douglas 1900 9/18/1965
Alsup, Johnny 1900 12/12/1968
Angles, Duane 1900 7/15/1952
Armentor, Colin 1900 5/29/1977
Arms, Jeffrey 1900 6/16/1965

I'ts probalby something simple, but I'll be darned if I can figure it out.

 

[SkipVought]

Check your Table Design. Is your BirthDate field defined as DATE?

Skip,

_[red][/red]

 

[southboy]

SkipVought, yes, the table birthdate field is set to Date/Time and the format is set to shortdate.

Southboy

 

[SkipVought]

I did a simple Query using your data...

SELECT
  Fullname
, BirthDate
, datediff('yyyy',Birthdate,date())

FROM `d:\My Documents\BD TEst`.`BDs$`

[tt]
Fullname BirthDate Expr1002
Adkins, Ronald 8/11/1949 58
Albright, Kerry, 4/12/1961 46
Alexander, Mike 9/6/1956 51
Allen, Brenda 6/27/1961 46
Allen, Douglas 9/18/1965 42
Alsup, Johnny 12/12/1968 39
Angles, Duane 7/15/1952 55
Armentor, Colin 5/29/1977 30
Arms, Jeffrey 6/16/1965 42
[/tt]
Please post your entire SQL.

Skip,

_[red][/red]

 

[southboy]

Your code only returned everyone as 1900 in AGE.

If I use "yyyy" instead of "d" it returns 1900 for everyone.

Here is the SQL code I'm using:
SELECT TokcPersonnel.Fullname, DateDiff('d',[Birthdate],Date()) AS Age, TokcPersonnel.BirthDate
FROM TokcPersonnel
ORDER BY TokcPersonnel.Fullname;

Southboy

 

[SkipVought]

here are the results of YOUR sql using 'd' in DateDiff...
[tt]
Fullname BirthDate 'Age'
Adkins, Ronald 8/11/1949 21144
Albright, Kerry 4/12/1961 16882
Alexander, Mike 9/6/1956 18561
Allen, Brenda 6/27/1961 16806
Allen, Douglas 9/18/1965 15262
Alsup, Johnny 12/12/1968 14081
Angles, Duane 7/15/1952 20075
Armentor, Colin 5/29/1977 10991
Arms, Jeffrey 6/16/1965 15356
[/tt]
which is the DAYS difference.

Are you sure that you do not have some FORMATTING going on in the display of your query data in the AGE column? Is it on a form or report?

Skip,

_[red][/red]

 

[SkipVought]

I took the number of days, from that last query and compared 'yyyy' DateDiff result with th number of days/365...
[tt]
'Age'
58 57.92876712
46 46.25205479
51 50.85205479
46 46.04383562
42 41.81369863
39 38.57808219
55 55
30 30.11232877
42 42.07123288
[/tt]


Skip,

_[red][/red]

 

[southboy]

If your getting something different than I am, then there must be some setting in my MS Access that is changing the way dates are set. Most of the code I have looked at is they way you are stating, but gives me different results.
I did find this code and it seemes to work, but it also checks for invalid dates.
Code that is working:
Age: IIf(Nz([birthdate],"")="","",DateDiff("yyyy",[birthdate],Now())+(Now()<DateSerial(Year(Now()),Month([birthdate]),Day([birthdate]))))
I had to change all my forms and reports to this code to get them to work correctly.

Southboy

 

[PHV]

FYI, the last code you posted should work in the same manner with ac97 or ac2003 ...
Thus, your original question still seems strange ...

 

[southboy]

I agree, it does seem strange. Maybe someone reading this thread along the way to a fix for their problem will know what the deal is and let us know. In the mean time, the last code I posted is working, although I don't know why since it is using "yyyy" and the others using "yyyy" wouldn't.

Thanx for your help.
Southboy

 

[SkipVought]

"Are you sure that you do not have some FORMATTING going on in the display of your query data in the AGE column? Is it on a form or report?"

Skip,

_[red][/red]