Link to home
Start Free TrialLog in
Avatar of Gwynneth Taylor
Gwynneth TaylorFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of sunezapa
sunezapa

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

Open in new window

Avatar of Gwynneth Taylor

ASKER

Thanks but I must be doing something incorrectly as i am getting -1 figures.
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?
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!



SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or, for the SQL of that query:

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

/gustav
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?

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.
 
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
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.
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?




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]'));


ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> .. 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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
cactus data

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

/gustav
I got there in the end. Excellent support from the Experts.  Grateful thanks to you all.