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
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
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.
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
mlmcc
ASKER
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
Are you seeing all the records?
mlmcc
ASKER
the data source is a single excel table/sheet.
I am seeing all the records, except for the dates previously mentioned.
I am seeing all the records, except for the dates previously mentioned.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
there are no empty rows
ASKER
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!
The TBDs are not recognized, but instead are null values, which I can work with.
SOLUTION!
ASKER
I really appreciate the extra help as the nature of the problem shifted.
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