Find records where value is out of a range

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,

Something like this ought to do it:

SELECT IDField, AdmitDate, DischargeDate, ServiceDate
FROM tblMyData
WHERE [ServiceDate][AdmitDate] Or [ServiceDate][DischargeDate]
ORDER BY IDField;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Chuck W" wrote in message
...
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,

"Chuck W" wrote in message
...
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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Arvin Meyer [MVP]" wrote in message
...
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)