Solved

Access 2003 db Query for DOB

Posted on 2013-01-29
11
253 Views
Last Modified: 2013-02-16
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
Comment
Question by:mig1980
  • 6
  • 4
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38833421
int ((dateserial(2012,11,01) - ID.DOB)/365.25)   as AGE
0
 

Assisted Solution

by:mig1980
mig1980 earned 0 total points
ID: 38833447
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 38834378
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
 

Author Comment

by:mig1980
ID: 38836038
Can you explain to me on what cases it will fail? I know it won't account for leap year but what else?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38836553
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:mig1980
ID: 38836589
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38836649
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
 

Author Comment

by:mig1980
ID: 38836714
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 38837157
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
 

Author Comment

by:mig1980
ID: 38837197
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
 

Author Closing Comment

by:mig1980
ID: 38896199
I ended up using my own answer but other answers were good as well.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Inner Join - Multiple Join Parameters 2 23
SQL Query Syntax Join 4 34
access 7 25
subform is not filtered by link field 8 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now