Text/Data formula problem

[michpaus]

I need to create a formula that will look at a field ( Expiry Date) and display items regardless of the data type. Unfortunately some of the dates were entered as date/time and others were entered as text. The report is pulling the information from a main frame database where dates are entered by end users.
I want te formula to say
If Expiry Date = text then display
else
If expiry date = date/time then display

Please help I am drawing a complete blank.

 

[synapsevampire]

Data returned by a database is always the same, the format is the only thing that might change.

The first thing to do is right click the field and select browse data, this will tell you the field type (probably String).

Now use a formula to test it's validity, such as:

// assuming a format of YYYYDDMM HHMMSS
Stringvar MyDate := {table.field};
if val(left(MyDate,4)) < 2500
and
val(mid(MyDate,5,2)) in 1 to 12
and
val(mid(MyDate,7,2)) in 1 to 31
then
cdate(val(left(MyDate,4)),val(mid(MyDate,5,2)),val(mid(MyDate,7,2)))

You'll need other checks.

It would be best to get your dba to run tests and clean up from the database side.

-k

 

[michpaus]

Thanks, will do!