Error Handling: Verify date-as-text format before or at cast?

[kdorian]

Is there any way to bypass or substitute garbage data before, or while, using a case statement? I CANNOT alter the data in the database itself.

The data I'm pulling has a text field that 99% of the time is in the format MM/DD/YYYY, but sometimes ends up with garbage data. When I am pull out the DoM, Month, and Year and cast them as integers for later calculation, the report crashes if any data in that field isn't formatted correctly. What I would LIKE to do is, if the data is not in the default format, substitute dummy text instead, like "01/15/1900", which not jack up any of the casts, and will will flag the data as something that has to be corrected by hand in the Excel file that is produced.

Is there some way of testing the field to be sure the data is in the right format? That would let me substitute my dummy date with a simple if-then-else statement.  If not, is there a way to handle an error with a case statement so the report doesn't crash?

[MFGF]

Is there any way to bypass or substitute garbage data before, or while, using a case statement? I CANNOT alter the data in the database itself.

The data I'm pulling has a text field that 99% of the time is in the format MM/DD/YYYY, but sometimes ends up with garbage data. When I am pull out the DoM, Month, and Year and cast them as integers for later calculation, the report crashes if any data in that field isn't formatted correctly. What I would LIKE to do is, if the data is not in the default format, substitute dummy text instead, like "01/15/1900", which not jack up any of the casts, and will will flag the data as something that has to be corrected by hand in the Excel file that is produced.

Is there some way of testing the field to be sure the data is in the right format? That would let me substitute my dummy date with a simple if-then-else statement.  If not, is there a way to handle an error with a case statement so the report doesn't crash?

Assuming the item is a character field currently, you could set up an expression that tests for the different parts being valid, using the substring() function. Sadly, the forum blocks me from posting up a sample expression. The approach I used was:
Test the first two characters to make sure they are not less than '01' or greater than '12'
Test the third and sixth characters to make sure they are the forward slash character
Test characters 7 to 10 to make sure they are not less than '1900' and not greater than '2021'
Test characters three and four to make sure they are not less than '01' and not greater than '31' or '30' for months 4,6,9 and 11 or 28 for month 2 not in a leap year or 29 for month 2 in a leap year.
For any failure above, set the value to be your default date, otherwise set it to be the value in the item.

There are probably more efficient ways to do this, but it's a start...

Cheers!

MF.

[kdorian]

I'll give it a shot, thank you!