getting records between 2 dates

[byurow]

hey everyone,

I have a form that enters patient assessment information. On this form, I want to pull in any hospital visits the patient may have had since the last assessment. I got this part working great through queries (thanks to some help in the query forum). Now, what I am trying to do is a bit more complicated. On the form, the user selects the patient, then selects the assessment they wish to view (it may not be the last assessment done, but an earlier one).

Once the user selects the patient, then selects the assessment they wish to view, the data fills in (unbound form). This works great. What I need to do is only pull in those hospital visits that occurred between the assessment date selected and the one prior to it. I have a list box on the form that fills based on a query with the PatientID as the parameter.

Any suggestions? Is what I am trying to do even possible?

Thanks!

Brenda

 

[sillysod]

you could have the form prompt the user for a date range as the form opens

in your queries criteria box put [Please enter start date]<[please enter end date]

this would find everything greater than the date user enters in the first prompt and less than (before) the latter date.

 

[byurow]

Unfortunately, my client does not want this. He wants it all done behind the scenes

Thanks for the reply though!

Brenda

 

[galileeian]

It should be possible. I think the easiest way would be with sql, using a nested sql statement.

Firstly write the SQL, or use query builder so that you have a query that gives the information between two dates, one of these can be from the form, and another would be a parameter.

Now you need to replace the parameter with another query that gives the required date ie somthing in the form of:
(SELECT MAX(date)
FROM table
WHERE patientId = id and date < dateOnForm )
I think you can paste little querys like this into the query builder.

 

[Golom]

I think that I answered this on thread701-764065 ... or is this some other issue?

 

[byurow]

Thanks galileeian, Golom answered the question for me in the Query forum.

Thanks again Golom!

Brenda