[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-22
11
Medium Priority
?
4,270 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:Mikeyro14
  • 6
  • 4
11 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 26105586
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
0
 

Author Comment

by:Mikeyro14
ID: 26105939
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26106129
Are you filtering the data in any way?

mlmcc
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mikeyro14
ID: 26106213
no
0
 
LVL 13

Expert Comment

by:PCIIain
ID: 26106452
What is the datasource? Is it an ODBC connection, or a link to excel, or XML?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26106520
Is this a single table?

Are you seeing all the records?

mlmcc
0
 

Author Comment

by:Mikeyro14
ID: 26106737
the data source is a single excel table/sheet.

I am seeing all the records, except for the dates previously mentioned.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 26109508
Is there data in all rows for that column? or at least in the first 5-10 rows?
Crystal treats Excel a little funny.  If there is no data in the first few rows then the column is ignored.

mlmcc
0
 

Author Comment

by:Mikeyro14
ID: 26112245
there are no empty rows
0
 

Author Comment

by:Mikeyro14
ID: 26112324
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!
0
 

Author Closing Comment

by:Mikeyro14
ID: 31669012
I really appreciate the extra help as the nature of the problem shifted.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question