Find records where value is out of a range

[Chuck W]

Hi,

I have three date fields with values in text format that I cannot change.
They appears a 03012009, 04142009 etc. The three fields are AdmitDate,
DischargeDate and ServiceDate. I want to identify all records in which the
service date is not between the admit and discharge date. So if I have a
service date of 05102009 an admit date of 03012009 and a discharge date of
04142009, the service date is out of range and I want it to appear in my
query. Can someone help?

Thanks,

 

[Arvin Meyer [MVP]]

Something like this ought to do it:

SELECT IDField, AdmitDate, DischargeDate, ServiceDate
FROM tblMyData
WHERE [ServiceDate]<[AdmitDate] Or [ServiceDate]>[DischargeDate]
ORDER BY IDField;

 

[Dirk Goldgar]

Hi,

I have three date fields with values in text format that I cannot change.
They appears a 03012009, 04142009 etc. The three fields are AdmitDate,
DischargeDate and ServiceDate. I want to identify all records in which
the
service date is not between the admit and discharge date. So if I have a
service date of 05102009 an admit date of 03012009 and a discharge date of
04142009, the service date is out of range and I want it to appear in my
query. Can someone help?

Assuming (1) the fields are always 8 digits in "mmddyyyy" format, and (2)
there are no Nulls in these fields, and (3) DischargeDate is always on or
after AdmitDate, then you might use a WHERE clause like this:

WHERE Not (CDate(Format(ServiceDate, "00/00/0000")) Between
CDate(Format(AdmitDate, "00/00/0000"))
And CDate(Format(DischargeDate, "00/00/0000")))

 

[Dirk Goldgar]

Something like this ought to do it:

SELECT IDField, AdmitDate, DischargeDate, ServiceDate
FROM tblMyData
WHERE [ServiceDate]<[AdmitDate] Or [ServiceDate]>[DischargeDate]
ORDER BY IDField;

That won't work if the dates are stored in text fields, as Chuck said they
were, Arvin.