Filtering query results based on a calculated field. Please help!!! 1

[spurs100]

Hi all,

Can anyone help me!!

I have a table named appointments with the following fields:

Patient ID|Appt ID|Appt Date|

I use all this fields but add a new calcualted field called New Appointment[Appointment Date]+ 84) to run a query,which produces the following results:

e.g

Patient ID|Appt ID|Appt Date| New Appointment

1 1 01/01/2002 26/03/2002
2 2 05/05/2004 28/07/2004
3 3 06/01/2002 31/03/2002
4 4 06/02/2002 01/05/2002
5 5 03/05/2002 26/07/2002
6 6 15/01/2002 09/04/2002

What i want to do is filter the results based on a date range for the New Appointment field e.g Between #01/01/02# AND #01/04/02#. this works fine if i enter this in the criteria box, however i want to get user input to select the date range using a form. the problem i am having is that when the user selects the date range using a form the table is not being filtered using the selected date range.

Please Help!!!

thanks in advance

 

[Quehay]

Criterion:
[tt]
Between forms!YourForm!txtStartDate and forms!frmYourForm!txtStopDate

[/tt]

 

[Darrylles]

Hiya,

I don't think that setting criteria for calculated fields sounds very feasible (or reasonable).

I think that you should create a new query that uses the query above as it's source (get rid of your range check on that query).
Select all of the fields that the first query returns.

As a quick check to see if it works, type this in the criteria for the 'New Appointment' field in query 2:

BETWEEN [From:] AND [To:]

Run the query - you may already know this, but you'll be prompted for the From date and then the To date.

If this works (which it should), then incorporate your 'Form' supplied dates as the criteria for the 'New Appointment' field in the second query. (That's if you want to, I think that the query 'Prompt' is quite sufficient, however, you may want it to be 'pretty'!)

Hope this helps.

Regards,

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[spurs100]

thank you both for your help,

However it still does not work. it works fine on a normal field but it wont use the date range to filter the calculated field, it just returns all the records, dont know why this is happening, like i said earlier it works fine if i put the date range directly into the criteria box,

I am trying to avoid making the user having to open the query each month and modyfying the date range in the criteria box, Any ideas?

thanks again

 

[JaneC]

Hi there,

the problem is that the query doesn't know that your calculated field is a date. To use the data on the form you need to :

change the format of the calculated field to a date format (if you haven't already done so)

Put Between [Forms]![Form2]![stdate] And [Forms]![Form2]![edate] in the criteria box

go into the query parameters box and define each of the criteria as DAte /Time

i.e
[Forms]![Form2]![stdate] , and
[Forms]![Form2]![edate]

HTH

Jane