jchangmwmc

asked on

# how do you change the data type of one cell in a colomn on crystal report

on my crystal report, one row of the imported fields need to different data type than the rest of the cells in the colomn. How do you do that?

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Place the field on the report canvas,

Right-click, Format Field, Common Tab

Enter an expression for the 'Display String' property, such as:

IF NumericText({file.REFERENCE}) Then

ToNumber({file.REFERENCE})

Else

0;

Right-click, Format Field, Common Tab

Enter an expression for the 'Display String' property, such as:

IF NumericText({file.REFERENC

ToNumber({file.REFERENCE})

Else

0;

ASKER

Thank you all for your quick responses. Let me add a little details: what I am trying to do is

I imported an excel file into crystal report. everything went well except that one row of each column has different data type than the rest of the documents. It has to be date data type rather than number data type in order to show correct info on the report. It should look like this:

Jan-12 Jan-13 Jan-14 Jan-15 Jan-16

123 456 456 786 555

right now the dates row shows as a number: 42156 which is the crystal conversion of the month and year into a number.

I appreciate all your help, but feels easier to try mlmcc's and idomillet's. will update the result

I imported an excel file into crystal report. everything went well except that one row of each column has different data type than the rest of the documents. It has to be date data type rather than number data type in order to show correct info on the report. It should look like this:

Jan-12 Jan-13 Jan-14 Jan-15 Jan-16

123 456 456 786 555

right now the dates row shows as a number: 42156 which is the crystal conversion of the month and year into a number.

I appreciate all your help, but feels easier to try mlmcc's and idomillet's. will update the result

A date in Excel is actually a number disguised as date.

It is the number of days since January 1, 1900

You can reverse that logic in Crystal using a formula such as:

DateAdd('d', {your_excel_date_column}, #1/1/1900#)

For example, DateAdd('d', 42017, #1/1/1900#) returns January 15, 2015

You can test to see if a value is a Date using the IsDate() formula.

It is the number of days since January 1, 1900

You can reverse that logic in Crystal using a formula such as:

DateAdd('d', {your_excel_date_column}, #1/1/1900#)

For example, DateAdd('d', 42017, #1/1/1900#) returns January 15, 2015

You can test to see if a value is a Date using the IsDate() formula.

ASKER

i tried to use both mlmcc's and idomillet's methods, but didn't work. Would you please be more specific on how to set first row of each column as date data type and rest of the the columns as numeric data type?

thanks!

thanks!

mlmcc already gave you the solution for that:

```
If RecordNumber = 1 then
{YourTextField}
Else
CStr({YourNumberField})
```

If you need a date as first row then numbers after that

Eric's formula needs a minor change. You need to convert both to strings

Add that formula to the report then put it where you want the data displayed.

mlmcc

Eric's formula needs a minor change. You need to convert both to strings

```
If RecordNumber = 1 then
CStr({YourField},"dd-MMM-yyyy")
Else
CStr({YourField})
```

Add that formula to the report then put it where you want the data displayed.

mlmcc

Do you need to use that date as a date value (eg. to compare it with another date), or do you just want to show the date on the report? Similarly, do you need to use the number as a number (eg. to calculate a total), or do you just want to show it on the report?

If you just want to show that value (date or number) on the report, you can use a formula like the last one that mlmcc posted, but you need to convert the number to a date first, and then convert that date to a string. Combining mlmcc's and Ido's suggestions:

If RecordNumber = 1 then

CStr (DateAdd ('d', {your field}, #1/1/1900#), "MMM-dd")

Else

CStr ({your field})

You can change the CStr arguments to change the date or number format.

Assuming that Ido was correct about how to convert the Excel number to a date, a slight variation on that would be:

If RecordNumber = 1 then

CStr (Date({your field} + 2), "MMM-dd")

Else

CStr ({your field})

The CR Date function will convert a number to a date, but for some reason it uses the number of days since 12/30/1899, instead of 01/01/1900, so you need to add 2 days.

As Ido mentioned, if there's any doubt about the numbers giving you valid dates, you can use the IsDate function to test them first, to avoid getting an error.

James

If you just want to show that value (date or number) on the report, you can use a formula like the last one that mlmcc posted, but you need to convert the number to a date first, and then convert that date to a string. Combining mlmcc's and Ido's suggestions:

If RecordNumber = 1 then

CStr (DateAdd ('d', {your field}, #1/1/1900#), "MMM-dd")

Else

CStr ({your field})

You can change the CStr arguments to change the date or number format.

Assuming that Ido was correct about how to convert the Excel number to a date, a slight variation on that would be:

If RecordNumber = 1 then

CStr (Date({your field} + 2), "MMM-dd")

Else

CStr ({your field})

The CR Date function will convert a number to a date, but for some reason it uses the number of days since 12/30/1899, instead of 01/01/1900, so you need to add 2 days.

As Ido mentioned, if there's any doubt about the numbers giving you valid dates, you can use the IsDate function to test them first, to avoid getting an error.

James

You could also use a formula like this. If you wanted say the 10th record to use a text field instead of a number field

If RecordNumber = 10 then

{YourTextField}

Else

CStr({YourNumberField})

mlmcc