Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 30th, 2003, 06:00 AM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query based on date calculation?

Hi,
Please can you help an amateur database dabbler?
I need to sort patients in my database by age at the date of data entry. The age has not been entered so this has to be done indirectly from their date of birth and date entered, values that are stored in different tables. I had thought i could calculate age in a query but cannot find a way of doing this, am I missing something?
I have made an unlinked text box on my data entry form that calculates age from these 2 parameters but cannot get this calculation to work in a query. Also I cannot find how to pass this calculated value back into a field in an underlying tabe so that this value can be stored for future use. Would this be the correct way to resolve the problem? If so can I update the table for the patients already in the database.
I'm sure there must be a very simple answer to this problem but it is beyond me! Any guidance most gratefully received.
Many thanks
Skinny


  #2 (permalink)  
Old September 30th, 2003, 06:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Skinny,

The following query will get you age in years:

SELECT PatientID, PatientDOB, Int((DateDiff("s",[PatientDOB],Date())/31556952)) AS AgeInYears
FROM Patients;

If you need age in months if the patient isn't a year old yet, the following function will get either age in months or age in years for you, which ever is applicable. You can call the function from your query:

Function AgeInYears(date1 As Date, date2 As Date) As String
If Int((DateDiff("s", date1, date2) / 31556952)) < 1 Then
    ' If less than 1 year, display months
    AgeInYears = Int((DateDiff("m", date1, date2))) & " mo."
Else ' Display years.
    AgeInYears = Int(DateDiff("s", date1, date2) / 31556952) & " yr."
End If

End Function

Call this function with:

SELECT PatientID, PatientDOB, AgeInYears(PatientDOB,Date()) AS AgeInYears
FROM Patients;

Since 'age' is a calculated value you don't need to store it in a table. Just generate it using the query when you need it.

HTH,

Bob




  #3 (permalink)  
Old September 30th, 2003, 07:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

ooops...I just noticed you wanted from date of entry. Just replace the Date() function in the queries (that gives you the current date) with a field from your base table that stores your AdmitDate. So you'll just need to store the DOB and AdmitDate in the table to calculate age.

:)

  #4 (permalink)  
Old September 30th, 2003, 07:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

To combine two tables, use somthing like:

1. Query without function call

SELECT Patients.PatientID, Patients.PatientDOB, Admissions.AdmitDate, Int((DateDiff("s",[PatientDOB],[AdmitDate])/31556952)) AS AgeInYears
FROM Patients INNER JOIN Admissions ON Patients.PatientID = Admissions.PatientID;

2. Query with function call

SELECT Patients.PatientID, Patients.PatientDOB, Admissions.AdmitDate, AgeInYears([PatientDOB],[AdmitDate]) AS AgeInYears
FROM Patients INNER JOIN Admissions ON Patients.PatientID = Admissions.PatientID;

The function itself doesn't need to change.

Sorry about that. I was running out the door and didn't read your post carefully enough.

Bob


  #5 (permalink)  
Old September 30th, 2003, 02:38 PM
Authorized User
 
Join Date: Jun 2003
Location: Swansea, , United Kingdom.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Bob,
These forum sessions are just what a beginner needs. Just hope I can remember it for next time.
Best wishes
Skinny

  #6 (permalink)  
Old September 30th, 2003, 03:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Quote:
quote:
These forum sessions are just what a beginner needs. Just hope I can remember it for next time.
Us slightly further along beginner's would have a hard time working without them too! :)

Bob



Similar Threads
Thread Thread Starter Forum Replies Last Post
Generic Date Calculation arnabghosh Javascript 0 April 10th, 2008 01:56 AM
Date Range Calculation connbound SQL Server 2000 2 October 5th, 2005 08:22 AM
Help needed with calculation in a query Lovegroover Access 6 August 18th, 2005 10:57 AM
Date based query when date is nvarchar MichaelTJ SQL Language 4 January 12th, 2004 09:57 PM
Results based on Date hcweb Classic ASP Basics 1 August 12th, 2003 02:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.