Invalid Military Time Entries

[shipcd]

I have a database which contains invalid military time entries. I do not have any opportunity to "clean" the data before run-time, therefore, the scheduled report fails when selecting on COMPLETIONDATE in LASTFULLMONTH.

Example bad entries:
0779
2365
0099

Any ideas how I can direct the query to skip "bad" or inaccurate military entries?

Crystal Reports 10, Enterprise 10, Progress Datasource

 

[hneal98]

What are some examples of what you consider valid military time?

 

[shipcd]

0800 = 8:00 am
0130 = 1:30 am
1330 = 1:30 pm
1359 = 1:59 pm

Basically, need to be able to ignore any invaild entries when pulling or reviewing the data. Thanks for your assistance!

 

[vidru]

If your connectivity type supports it, you can create SQL Expressions - one that gets the last two 'digits' of the field (I assume it's a string), and the other to make sure the field is numeric.

I've never used Progress, so I don't know what the exact syntax would be, but it would look something like this:

Name the first SQL Expression 'Last2Digits', and give it this text:
Right(Table.Field, 2)

Name the second one 'IsNumeric', and give it this text:
IsNumeric(Table.Field)

Then alter your record selection formula to check both of these conditions, and only return the ones that are valid:
{%Last2Digits} < '60' and {%IsNumeric} = 1

The main benefit here is that the processing is done on the server, whereas if you're not able to create SQL Expressions, the processing would take place in Crystal.

If you are indeed unable to use SQL Expressions, you could use a record selection formula like the following:
Val(Right({Table.Field}, 2)) < 60
and
IsNumeric({Table.Field})

-dave

 

[jjames]

Paste the code into your Record Selection Formula and change {Table2.MilitaryTime}.

if Length({Table2.MilitaryTime}) = 4 AND NumericText ({Table2.MilitaryTime}) then (
    Local NumberVar HH := Val(Left({Table2.MilitaryTime},2));
    Local NumberVar MM := Val(Right({Table2.MilitaryTime},2))
)
Else (
    Local NumberVar HH := 99;
    Local NumberVar MM := 99
);

(HH = 24 AND MM = 0)
OR
(
  HH in (0 to 23)
  AND
  MM in (0 to 59) 
)