Query too complex (#Error) - but it isn't!!

[UHNSTrust]

I am trying to do a simple query. I have a dataset which is 2 fields (AdmissionDate and DischargeDate). I want to be able to run a query where I supply a criteria (date) at run time which then pulls back records where the AdmissionDate is < the criteria date and the DischargeDate is > the criteria date. I have that cracked.

SELECT [CensusDate] AS Census, Non_Elective_Spells_0910_1011.AdmissionDate, Non_Elective_Spells_0910_1011.DischargeDate
FROM Non_Elective_Spells_0910_1011
WHERE (((Non_Elective_Spells_0910_1011.AdmissionDate)<[CensusDate]) AND ((Non_Elective_Spells_0910_1011.DischargeDate)>[CensusDate]));

What I then want to know is the amount of days between the AdmissionDate and the criteria date. Finally, I only want to show records where the amount of days is >14. The SQL I have at the moment is;

SELECT [CensusDate] AS Census, Non_Elective_Spells_0910_1011.AdmissionDate, Non_Elective_Spells_0910_1011.DischargeDate, [CensusDate]-[AdmissionDate] AS LOS
FROM Non_Elective_Spells_0910_1011
WHERE (((Non_Elective_Spells_0910_1011.AdmissionDate)<[CensusDate]) AND ((Non_Elective_Spells_0910_1011.DischargeDate)>[CensusDate]) AND (([CensusDate]-[AdmissionDate])>14));

This gives an error which says the expression is typed incorrectly or is too complex. If I take the criteria of '([CensusDate]-[AdmissionDate])>14' out then it will run but give me #Error where I am expecting a number.

Can anyone put me in the right direction? Thanks.

Jonathan

 

[PHV]

what about this ?

PARAMETERS [CensusDate] DateTime;
SELECT [CensusDate] AS Census, AdmissionDate, DischargeDate, [CensusDate]-AdmissionDate AS LOS
FROM Non_Elective_Spells_0910_1011
WHERE (AdmissionDate+14)<[CensusDate] AND DischargeDate>[CensusDate]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886