Access date query

I have the following column in my access query to store the date of a patient.

Age: DateDiff("yyyy",[PatientDateofBirth],Date())

How can I complete a parameter query to select patients < than or > than a particular age rather than storing a specific value in the query.
Thanks
Gwynneth TaylorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
sunezapaCommented:
use the same code as in your "age" field ?
SELECT * FROM mytable 
WHERE DateDiff("yyyy",[PatientDateofBirth],Date()) <> [ParticularAge]

Open in new window

0
 
Gwynneth TaylorAuthor Commented:
Thanks but I must be doing something incorrectly as i am getting -1 figures.
0
 
sunezapaCommented:
Are you making the select in  a query or from VBA code?

for the query: it should ask you to enter the age  (you would call it from a form and change the ParticularAge to a form field...)

for the VBA: What is your complete code, related to this Select-call?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gwynneth TaylorAuthor Commented:
Thanks, I am selecting from a query.  The form is frmPatientInformation and the text field on that form is txtAge.

As suggested I have changed the Select statement to:
Age: DateDiff("yyyy",[PatientDateofBirth],Date())<>[frmPatientInformation.txtAge]

The criteria is [Please enter patient age].
I am still wrong,sorry but only learning!



0
 
Gustav BrockCIOCommented:
You will need a function like this to calculate age correctly:
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 'datDateCurrent ' 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

Then in your query:

Age: AgeSimple([PatientDateofBirth])

Add the criteria you wish for this column.

/gustav
0
 
Gustav BrockCIOCommented:
Or, for the SQL of that query:

Select *, AgeSimple([PatientDateofBirth]) As Age
From tblYourTable
Where AgeSimple([PatientDateofBirth])<>[frmPatientInformation.txtAge]

/gustav
0
 
sunezapaCommented:
I am not sure if you want to show the age in a field on the form,
or you want to select/show all the records where age is different from a particular age?
0
 
Gwynneth TaylorAuthor Commented:

Sunezapa - the age field is currently showing on the form with =Int((DateDiff("y",[PatientDateOfBirth],Now()))/365.25) in the control source of txtAge.  I have a command button on a separate form to open queries and reports.  The command button opens the query to display a parameter to select patient by diagnosis and then age.  At present if I enter the diagnosis and exact age the query runs fine.  I just want to change this to be able to enter the criteria diagnosis followed by either exact age, greater than or less than a particular age. Thanks.

cactus data

I have tried your method and placed your code in a module? I have then used  =AgeSimple([PatientDateofBirth]) as the control source on txtAge on the form.  This is not displaying the age but showing as name in the text box. Can you please tell me what I am doing wrong here? Thanks.
 
0
 
Gustav BrockCIOCommented:
I don't know. Perhaps you didn't compile and save the module?

If property ControlSource of txtAge really is:

=AgeSimple([PatientDateofBirth])

the textbox cannot display the name of the function.

/gustav
0
 
Richard DanekeTrainerCommented:
I think you were real close at this point using the QBE -Query Design form or Query By Example form instead of typing SQL:
Thanks, I am selecting from a query.  The form is frmPatientInformation and the text field on that form is txtAge.
As suggested I have changed the Select statement to:
Age: DateDiff("yyyy",[PatientDateofBirth],Date())<>[frmPatientInformation.txtAge]
The criteria is [Please enter patient age].
I am still wrong,sorry but only learning!
Using the original code: Age: DateDiff("yyyy",[PatientDateofBirth],Date()) to define the column.  
Using Query Design form, you place the parameter in the Criteria row for this column.   So, the criteria is [Please enter patient age].
The function offered is good for calculating a more exact age, but may not be needed for your report.
0
 
RgGray3Commented:
How about placing a field in your query with the following  

FieldName: DateDiff("yyyy",[PatientDateofBirth],Date()) <[Enter Age to be considered]
It would prompt you for an age for comparison
Should return a true or false

Value from any of the above functions could be evaluated the same way

Now you see to want a little more flexability  < > =  or possibly BETWEEN X and Y

When I need that type of flexability I resort to Dynamic SQL (creating it on the fly) or "Query by Form"

before I would go into too much detail there...  Is that something you would feel comfortable with?




0
 
Gwynneth TaylorAuthor Commented:
Great thanks I am slowly getting there!  I did compile but must not have saved - it is now working now to show the age on the form using your function.  

However I am now attempting the query but still not right.  Can I amend the following statement so that I can put in the parameter box for example <35 or greater than 20.  It is still only displaying exact values.

SELECT tblPatient.PatientID, tblPatient.PatientSurname, tblPatient.PatientPrimaryDiagnosis, AgeSimple([PatientDateofBirth]) AS Age
FROM tblPatient
WHERE (((AgeSimple([PatientDateofBirth]))=[Please enter age] And (AgeSimple([PatientDateofBirth]))<>'[frmPatientInformation.txtAge]'));


0
 
sunezapaCommented:
you can not have at same time "age = value" and "age <> value" as result always will null
possibilities: create different queries
but better: enter the options in the form with all choices: equal, less than, greater than (and the age).
Depending on that input you call the query with a parameter  that inlcude the choice (less than, etc.)

If you just want to show the result in the form you may just create the compolete SQL in code and use it as source for a textbox or lsitbox or whatever. (this is if you do not need to work with the result in a query-window...)
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Gustav BrockCIOCommented:
> .. for example <35 or greater than 20.

Yes:

Select
  *, AgeSimple([PatientDateofBirth]) As Age
From
  tblYourTable
Where
  AgeSimple([PatientDateofBirth]) > [frmPatientInformation.txtAgeMin]
  And
  AgeSimple([PatientDateofBirth]) < [frmPatientInformation.txtAgeMax]

/gustav
0
 
Gwynneth TaylorAuthor Commented:
Thank you sunezapa - I will take your advice and enter the options in the form with all choices.  

Thank you cactus data for help with the function and also to other Experts for your time and input to help me.
Gwynneth
0
 
Gwynneth TaylorAuthor Commented:
cactus data

I have just seen your option above so will try that out. Thanks.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
Gwynneth TaylorAuthor Commented:
I got there in the end. Excellent support from the Experts.  Grateful thanks to you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.