Link to home
Start Free TrialLog in
Avatar of Mikeyro14
Mikeyro14

asked on

How do I change the data type of a field in Crystal Reports?

My data source is a spreadsheet extract. There are two fields with date values.  

The first, "Scheduled Date", contains only date values.  Crystal Reports recognizes this data type as DateTime (which is perfect).

The second, "Actual Date", contains date values and "TBD" values.  Changing these TBD values in the data source is not an option. Crystal Reports recognizes this data type as String (I want DateTime).

I would like to change the Actual Date field data type to DateTime so I can compare the two dates in a formula (if Actual Date > Scheduled Date .... etc.).

Please help

Avatar of Mike McCracken
Mike McCracken

You can't change the data type since it isnt a date in all cases.

What you can do is to use a formula then compare against the formula

If ({ActualDateField} <> 'TBD') then
     Date({ActualDateField})
else
     Date(1901,01,01)

You can use whatever date you want as the default for TBD.  If you want the compare to be false then use a date that is later than any date in the database like Date(2050,12,31)

Another way would be to do the compare as

If ({ActualDateField} <> 'TBD') then
    Date({ActualDateField})  > {ScheduledDateField}
else
    FALSE  //Use TRUE if that is the result you want.

mlmcc
Avatar of Mikeyro14

ASKER

That solution is perfect for the problem I posted; however I just discovered another level of complexity.  I thank you for your response and hope you will be able to further assist me...

It seems the real issue is that Crystal Reports is not recognizing the date values in the Actual Date field.  If I pop the field into my report (either straight from the data source or as the formula you just shared), only the TBDs/ future dates show up.  None of the other dates show up at all.  

If I could get these dates to show up, then my problem would be solved.
Are you filtering the data in any way?

mlmcc
no
What is the datasource? Is it an ODBC connection, or a link to excel, or XML?
Is this a single table?

Are you seeing all the records?

mlmcc
the data source is a single excel table/sheet.

I am seeing all the records, except for the dates previously mentioned.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
there are no empty rows
However, if I sort the spreadsheet on the date field before connecting to Crystal Reports (thus bringing the dates into the first few rows), it recognizes the field as DateTime.

The TBDs are not recognized, but instead are null values, which I can work with.


SOLUTION!
I really appreciate the extra help as the nature of the problem shifted.