Parameter query on expression field

[M. Halgas]

I am trying to set up a query/report for exam due dates. So, for instance,
individuals should have a dental exam every 6 months. I would like to run a
parameter query that will tell me all of the dental exams that are due for a
given month based on the last visit date. Currently I have an expression
built in the query that adds 180 days to the last visit date. I then used
the "between [start] and [end] function on the expression. The expressions
worked, but the parameter did not. I also tried a calculated field in the
form, but I couldn't figure out how to set a parameter on the calculated
field, either. Any suggestions on how to fix the query or come to the
correct result in a different way?

 

[Allen Browne]

I'm guessing that the match fails because Access is not interpreting the
data type correctly.

It can go wrong in the calculated field, or in the parameter. Best solution
is to be explitict about both.

Add CVDate() around your calculated date field to typecast the result to a
date. It will look something like this:
NextDue: CVDate(DateAdd("m", 6, [ExamDate]))

Now declare both parameters.
Choose Parameters on the Query menu (in query design.)
Access opens a dialog.
Add 2 rows like this:
[start] Date/Time
[EndDate] Date/Time

You also need to change your 2nd parameter name in the query. "End" is a
reserved word, so choose a different one such as EndDate. (The name in the
criteria of the query must be the same name you used when declaring the
parameter.)

For a list of names that can cause you problems, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

For more info about typecasting fields, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Ken Sheridan]

As you are want to return rows for a particular month you can do this more
easily by entering the year and month (as a number) as the parameters rather
than using a BETWEEN….AND operation. You can use the YEAR and MONTH
functions to compare the dates with the parameters.

You don't say how the data is stored, so I'll assume you have a Patients
table and an Examinations table related on PatientID. The latest examination
can be found by means of the MAX function applied to the examination date, so
if you add 6 months to this with the DATEADD function this gives you the date
when the patient's next examination is due.

Putting all this together a query might look something like this:

SELECT FirstName, LastName, Address,
DATEADD("m",6,MAX(ExaminationDate)) AS ExaminationDueDate
FROM Patients INNER JOIN Examinations
ON Patients.PatientID = Examimations.PatientID
GROUP BY FirstName, LastName, Address
HAVING YEAR(DATEADD ("m",6,MAX(ExaminationDate))) = [Enter Year:]
AND MONTH(DATEADD ("m",6,MAX(ExaminationDate))) = [Enter Month:];

Ken Sheridan
Stafford, England