Need more help regarding multiple parameters

  • Thread starter Thread starter tom
  • Start date Start date
T

tom

Hi im having lots of trouble with parameters

The first parameter works fine ([Show Closed Clients? Yes or No]) Or ([Show
Closed Clients? Yes or No] IS NULL)
Its the second and third that im having trouble with
Attendance.Date Between ([Enter First Date] And [Enter Last Date]) OR
(([Enter First Date]) Is Null) AND (([Enter Last Date]) Is Null)
For some reason this doesnt work.



Here is the Entire SQL statments

PARAMETERS [Show Closed Clients? Yes or No] Bit, [Enter First Date]
DateTime, [Enter Last Date] DateTime;
SELECT Attendance.Date, Attendance.Service, Attendance.Treatment,
Attendance.Notes, Person.closed, Person.Registration, Person.FirstName,
Person.Surname
FROM Person INNER JOIN Attendance ON Person.PersonID = Attendance.PersonID
WHERE (((Attendance.Date) Between [Enter First Date] And [Enter Last Date])
AND ((Person.closed)=([Show closed Clients? Yes or No]))) OR ((([Show closed
Clients? Yes or No] Is Null)) AND (([Enter First Date]) Is Null) AND
(([Enter Last Date]) Is Null));
 
Ive also tried to calculate the date like this

Between NZ([Enter First Date],#01/01/1750#) And NZ([Enter Last Date],
#01/01/9999#)

This works well on its own, but i still get problems when i try to run it
with another parameter that uses the Is Null clause.

The non-date parameter only has two possible answers yes and no. Is there
some other way of selecting all these records without using Is Null?
 
Back
Top