×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Datatype Mismatch

Excel Datatype Mismatch

Excel Datatype Mismatch

(OP)
Hi,

I'm using Crystal Reports 2013 Support Pack 11 on a 64-bit Windows 7 Professional Operating System. I'm creating a new report using an Excel spreadsheet as my data source. I am using the native Access/Excel (DAO) connection. The termination column in Excel is formatted as "Date". However, when browsing field data or placing field on to my report in CR, the field is a string. This occurs whether I use Excel 2013 or Excel 97-2003 workbook.

Any insight?

RE: Excel Datatype Mismatch

Hi,

The key bit of information is that Date in Excel is a NUMBER that is displayed as a date. That NUMBER is the number of days since December 31, 1899. So the NUMBER value for the date Jan 1, 1900, is 1.

Your BO system can't use the NUMBERS from Excel dates. It needs the STRING "1/1/1900" representation or some similar string.

My guess.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Datatype Mismatch

And thus if you want that 'date' to be a date, you can convert in Crystal Reports to a date.

RE: Excel Datatype Mismatch

(OP)
Thanks for the responses. However, this field was once recognized as a date in an existing report and is currently embedded in several formulas within that same report. I would rather not create a brand new formula converting the string to a date within Crystal and then have to find each place where the original field was referenced and replace it with the new formula field.

By the way, Crystal can recognize cells formatted as dates in Excel. When the spreadsheet is used as the datasource, those fields have a datetime datatype in Crystal. I have another report which does this perfectly. Not sure why the date format is recognized and retained from one spreadsheet and not from the other. I was curious to know if anyone had encountered a similar issue.

Thanks!

RE: Excel Datatype Mismatch

Not sure, I know that Crystal probably uses a similar engine as Excel does to try to determine the data type from the first few rows of data.

RE: Excel Datatype Mismatch

Okay, then do this in Excel to test whether you actually have DATES in Excel.

1) In Excel, select any cell containing a date in question
2) Right-click and select Format cells...
3) Select GENERAL in Category:
4) If the DATE does not change to a NUMBER, then you do not have DATES but STRINGS.

Post back for the next step if you have STRINGS in Excel.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Datatype Mismatch

(OP)


Skip, I previously formatted the entire column to date.

Following your instructions above, I right clicked on one cell which displayed the date 1/4/2019. In the Format Cells dialog box, I confirmed the category was "Date". I then changed the category to "General" and the number 43469 appeared.

I've attached screenshots to post.

I think perhaps it has something to do with the drivers on my machine......then I'm not sure about that because I would think it would affect all reports with Excel Data Sources and not this specific one.....

RE: Excel Datatype Mismatch

You appear to have exactly what I would expect to see with Excel dates.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close