Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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


Reply With Quote
  #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




Reply With Quote
  #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.

:)

Reply With Quote
  #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


Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:23 PM.


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