Find records where value is out of a range

C

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,
 
A

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;
 
D

Dirk Goldgar

Chuck W said:
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")))
 
D

Dirk Goldgar

Arvin Meyer said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top