Excel database as source data - problem with blank entries

[Former Member]

Hello.

I'm using Crystal Report version 11 and creating a report with data source in excel format.

As a brief, the excel database looks like below (to keep track of machines that have been sold and signed by customers);

<Machine No> <Sold Date> <Customer Signed Date>

Some machines are sold but customer hasnu2019t signed, hence the customer signed date is blank. Some machines are not yet sold so both Sold Date & Customer Signed Date are blank.

The problem I am experiencing is that, when Crystal Report reads data from Excel, if first records of the database have no date (ie. Customer Signed Dates are blank), Crystal Report doesnu2019t seem to recognise the field properly and all the remaining records have blank Customer Signed Date, even though there are some valid entries in the field.

Any help will be greatly appreciated. Thank you very much.

May

[Former Member · Accepted solution]

Sort your excel data so that first record always have data in firts row.

Crystal can then re-sort data as required.

Ian

[Former Member · Accepted solution]

Thank you Ian,

I had sorted data by "Sold Date" in descending order so Sold Date has always got a date on top, but sometimes Customer Signed Date hasn't got date even if Sold Date has got date. Then Sold Date is detected correctly but Customer Signed Date has no entries at all, because the first entry is blank...

The problem is that due to the database purpose, Sold Date can be left blank when Cusomer Signed Date has an entry..(!) - this is because you can enter 'planned' signed date. Therefore I cannot solve the problem by setting the sort order on Customer Signed Date descending - if I did and "Sold Date" was blank at first entry, then all records have no Sold Date..

May

[former_member292966 · Accepted solution]

Hi May,

I've run into this lots of times. The easiest way to deal with it is to have a sample record on the first line. The sample would have a value in each column. When you designe the report have the Record Selection Formula remove this record from the report.

Good Luck,

Brian

[Former Member · Accepted solution]

Hi Brian,

Thank you very much for your comment.

So it's a known problem..!

As you suggested, I have entered a dummy record with entries in all fields, and it works now!

Thank you very much again..

May