Query is pulling additional information (1 Viewer)

[srburk]

input fields:
DATE 12/25/2004
APPT 12:05 pm
ARVL 12:10 pm

APPTARVL is a calculation of the difference in minutes:
ApptArvl: DateDiff("n",[APPT],[ARVL])

The query will be used for reporting purposes...

When the query is opened, the user is asked for a date range:
Between [START DATE] And [END DATE]

It also asks for a data range relating to the ApptArvl.
Between [START RANGE] And [END RANGE]

Scenario:
The user enters 03/29 for the begin date and 03/30 for the end date.
The user then enters -60 for the begin range and 17 for the end range. (The user wants to produce a report showing all appointments arriving -60minutes < x < 17minutes).

My problem is that the report pulls up the correct date but also includes all appointments. It seems to ignore the second criteria. What am I doing wrong???

Scott

 

[RichO]

What is the syntax of your range criteria and what method does the user use to input it?

 

[srburk]

What is the syntax of your range criteria and what method does the user use to input it?

I apologize... I have no idea what you mean.

The DateDiff formula is:
ApptArvl: DateDiff("n",[APPT],[ARVL])

The results is the number of minutes between arrival and actual appointment time (negative numbers indicate the number of minutes early).

I would like for the user to filter and show only those patients that arrived up to 60 minutes early for their appointment and no later than 15 minutes after their appointment.

My criteria states something like:
Between [Enter start range] and [Enter end range]

The user would enter:
[Enter start range] -60
[Enter end range] 15

This would hopefully produce those patients with:
-60 <= x <= 15

Unfortunately, the query pulls all data.

On a possibly related note... even though I prompt the user to enter the begin and end date once, it may ask them 2 or 3 times.

Any clue???

Scott

 

[RichO]

I did a bit of toying around with this concept and it seems that the query doesn't assume that your entered parameter is numeric so you need to use the Val function to return a true number.

Between Val([Enter start range]) and Val([Enter end range])

 

[stinej]

Using Queries Parameters

Hi, I actually came across the same problem. I fixed it by opening my query in design mode -> selecting the query menu -> selecting parameters -> entering in my prompt names -> selecting the proper data type for the values I wanted to pass to the query. This fixed my problem as I was pulling more dates that I wanted as well. I hope this helps someone.

-J