mig1980
asked on
Access 2003 db Query for DOB
Good day everyone. I have a query which contains a IIF() statement to find a DOB of a person on our database. I am use to using SQL and so i tried to use CAST() but that would not work.
How can I rewrite this query to equate the SQL query I have here:
This is for Access 2003
How can I rewrite this query to equate the SQL query I have here:
CAST(floor((DateDiff(d,id.DOB,'2012- 11-01')/36 5.25)) as INT) as AGE
This is for Access 2003
peter57r
int ((dateserial(2012,11,01) - ID.DOB)/365.25) as AGE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can you explain to me on what cases it will fail? I know it won't account for leap year but what else?
It is as noted in-line:
' leap years
' dates of 29. February
' date/time values with embedded time values
In some cases this is not important at all (like sending birthday cards) but for insurance, employment, and similar it may be mandatory to have an exact measure.
/gustav
' leap years
' dates of 29. February
' date/time values with embedded time values
In some cases this is not important at all (like sending birthday cards) but for insurance, employment, and similar it may be mandatory to have an exact measure.
/gustav
ASKER
How would you modify the function above if you wanted the age as of a certain date and you had the DOB in a database?
For example:
Public Function AgeSimple( _
ByVal datDateOfBirth As Date,
ByVal datToday As Date) _
As Integer
and then comment out:
' Dim datToday As Date
And:
AgeSimple([Bdate], [SomeDate]) As Age
where [SomeDate] as a parameter of your query.
/gustav
Public Function AgeSimple( _
ByVal datDateOfBirth As Date,
ByVal datToday As Date) _
As Integer
and then comment out:
' Dim datToday As Date
And:
AgeSimple([Bdate], [SomeDate]) As Age
where [SomeDate] as a parameter of your query.
/gustav
ASKER
I won't be transferring the specific date parameter. It will be hard-coded. How can I hard-code it into the function for example: 10/1/2012
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. I didn't remember how to compose a specific date field in Access. It is the pound sign.
Thank you for your help. I will give this a try.
Thank you for your help. I will give this a try.
ASKER
I ended up using my own answer but other answers were good as well.