• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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:


CAST(floor((DateDiff(d,id.DOB,'2012-11-01')/365.25))  as INT) as AGE

This is for Access 2003
0
mig1980
Asked:
mig1980
  • 6
  • 4
3 Solutions
 
peter57rCommented:
int ((dateserial(2012,11,01) - ID.DOB)/365.25)   as AGE
0
 
mig1980Author Commented:
I ended up using - DateDiff("yyyy", [Bdate], Now())+ Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

And it seems to work well. Do you see any flaws with using what I am using as appose to what you posted?
0
 
Gustav BrockCIOCommented:
Except that it will fail for certain cases.
Use an external function like the one below:

AgeSimple([Bdate]) As Age

Public Function AgeSimple( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer
    
  datToday = Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears > 0 Then
    ' Decrease by 1 if current date is earlier than birthday of current year
    ' using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
  End If
  
  AgeSimple = intAge
  
End Function

Open in new window

/gustav
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
mig1980Author Commented:
Can you explain to me on what cases it will fail? I know it won't account for leap year but what else?
0
 
Gustav BrockCIOCommented:
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
0
 
mig1980Author Commented:
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?
0
 
Gustav BrockCIOCommented:
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
0
 
mig1980Author Commented:
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
0
 
Gustav BrockCIOCommented:
OK, then all you need is to specify that date in favour of today's date.
Change this line:

  datToday = Date

to:

  datToday = #10/01/2012#

/gustav
0
 
mig1980Author Commented:
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.
0
 
mig1980Author Commented:
I ended up using my own answer but other answers were good as well.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now