Invalid Date Search 1

[dixie617]

I am trying to find invalid dates entered into a field within our database. The field is called ObsValue, which can be anything, it is in a general table that houses all observations entered, from text, dates, numbers and so on. I have approximately 150 of these various terms that are entered in the table with an obsvalue that should be entered as dates. I am trying to figure out how to see just those entries that were done wrong, or entered incorrectly, like 00/00/00 or 09/09 and so on instead of a valid date. Is there a way that crystal 2008 can do this?

 

[lbass]

Is there another field that defines what type of value the ObsValue holds? Is the Obsvalue field a string? What is the valid format (use "MM/dd/yyyy", for example, to explain, please).

-LB

 

[dixie617]

There is another field that identifies what is supposed to be entered, but does not check for valid entry. Many moons ago, a few years now anyway, before the developing team got smart and set limits and formats on what could be entered, many were entered incorrectly. ObsValue is a string that I have to convert to a date, only after I ask for the specific value type and the only limit is the size of the field entry. Valid date format should be mm/dd/yyyy.

This table holds about 33 million rows in obsvalues, from blood pressure readings, lab results, estimated delivery dates (pregnancy), next check up due date, things like that, pretty much any observation or vital sign taken from patient by a nurse or provider. they are all labeled what they are in a seperate field called description or obsterm. there is about 150 of these descriptions that have coinciding obsvalues that should be dates. Does that make things a bit clearer?

 

[lbass]

Try a record selection formula like this:


{table.obsterm} = "Date" and
(
ubound(split({table.obsvalue},"/")) <> 3 or
len({table.obsvalue}) <> 10 or
not(val(split({table.obsvalue},"/")[1]) in 1 to 12) or
not(val(split({table.obsvalue},"/")[2]) in 1 to 31) or
not(val(split({table.obsvalue},"/")[3]) in 2000 to year(currentdate)) or //use valid values
(
val(split({table.obsvalue},"/")[1]) in [4,6,9,11] and
val(split({table.obsvalue},"/")[2]) > 30
) or
(
val(split({table.obsvalue},"/")[1]) = 2 and
val(split({table.obsvalue},"/")[2]) > 29
) or
not isnumeric(
split({table.obsvalue},"/")[1]) or
not isnumeric(
split({table.obsvalue},"/")[2]) or
not isnumeric(
split({table.obsvalue},"/")[3])
)

-LB

 

[dixie617]

Hey LB,

Sorry I did not get back to you on this, got stuck with a project that put everything on the back burner for a couple of months.

Now I am back working on this little project with a bit of a twist. Working in a different table, I am still looking for a formula that can search for invalid dates. Here is an example of the data that I have been getting the field is called the {PROBLEM.APROXONSETDATE} with many varied entries like below:

2006
07/2008
4 years
1985
1985
10/01/2008 (valid)
10/01/1999
02/20/2009
8/2008
1999
042005
06/2000
04/2009
04/2004
11/2004

What I want the report to show is the invalid date entries that are not like mm/dd/yyyy (which is the correct entry).

I tried the entry above but I must of done it wrong, it would not do anything or even save the formula.

Dix

 

[lbass]

Please show the formula you tried. This was intended as a record selecction formula. You have to substitute in your actual table.field names.

-LB

 

[dixie617]

LB,

This is my entire record selection formula, I did get the report to run without an error this time, but no data came up on the report:

{PROBLEM.APROXONSETDATE} = "Date" and
(
ubound(split({PROBLEM.APROXONSETDATE},"/")) <> 3 or
len({PROBLEM.APROXONSETDATE}) <> 10 or
not(val(split({PROBLEM.APROXONSETDATE},"/")[1]) in 1 to 12) or
not(val(split({PROBLEM.APROXONSETDATE},"/")[2]) in 1 to 31) or
not(val(split({PROBLEM.APROXONSETDATE},"/")[3]) in 2000 to year(currentdate)
) or //use valid values
(
val(split({PROBLEM.APROXONSETDATE},"/")[1]) in [4,6,9,11] and
val(split({PROBLEM.APROXONSETDATE},"/")[2]) > 30
) or
(
val(split({PROBLEM.APROXONSETDATE},"/")[1]) = 2 and
val(split({PROBLEM.APROXONSETDATE},"/")[2]) > 29
) or
not isnumeric(split({PROBLEM.APROXONSETDATE},"/")[1]) or
not isnumeric(split({PROBLEM.APROXONSETDATE},"/")[2]) or
not isnumeric(split({PROBLEM.APROXONSETDATE},"/")[3])
)
and
{PERSON.PSTATUS} = "A"
and
Not ( ({PERSON.LASTNAME}) like "zz*")
and
{PROBLEM.CODE} like ["ICD-401*", "ICD-250*"]
and
isnull({PROBLEM.ONSETDATE}) and
not (isnull({PROBLEM.APROXONSETDATE})) and
isnull ({PROBLEM.STOPREASON} ) and
//isnull ({PROBLEM.APROXSTOPDATE}) and
not ({PROBLEM.DESCRIPTION} like "History of*") and

not ({LOCREG.ABBREVNAME} like "AP*")
and
{PROBLEM.DB_CREATE_DATE} in [currentdate - 366 to currentdate]

Dix

 

[lbass]

I think you have used the wrong field in the first line:

{PROBLEM.APROXONSETDATE} = "Date" and

And your null checks MUST be placed at the beginning of the formula:

isnull({PROBLEM.ONSETDATE}) and
not (isnull({PROBLEM.APROXONSETDATE})) and
isnull ({PROBLEM.STOPREASON} ) and
//isnull ({PROBLEM.APROXSTOPDATE}) and

-LB

 

[dixie617]

Cool, got it to work, I just took out the first line, one more question I have a few that are like 1/1/2007, or 1/01/2007 which Crystal will read as a valid date correct? If so how do also illiminate those?

Thanks

Dix

 

[lbass]

I thought you had to specify:

{table.OBSTERM} = "Date"

Also, it is NOT optional to move the null tests--they MUST be first in the formula.

Those would be invalid because they don't have two digits in the month and/or day places. If you want to test how the formula is working, remove it from the selection formula and add it as a formula on the report--check for whether the true/false values are what you expect.

-LB

 

[dixie617]

I did put the null tests first in the formula, I did not realize that those should be first. Ok, that makes since that they are still invalid dates, I did test them, they come up false.

Thanks so much for all the help.