Blank dates display 1/0/00 1

[nb4884]

Need advise on :

Report displays 1/0/00 for all the blank dates when exported to xls data only format. How do i avoid getting 1/0/00 and just show the empty space where ever there are blank dates.

 

[IanWaterman]

In File -> Report Options options

Have you checked - Convert Database Null values to default

If yes, try unchecking

Ian

 

[nb4884]

It is unchecked , any other suggestions?

 

[IanWaterman]

Must be a setting in Excel. Is the field blank when you run report in Crystal?

Try converting date to string.

@Date
If isnull(datefield) then ' ' else
totext(datefield, 'dd/MM/yyyy')

Ian

 

[nb4884]

Thanks Ian, I tried the above formula as well. This however doesnot display 1/0/00 but my background color formula is showing me error.

here is my background color logic:

if Maximum(@Date, query1) < totext(printdate, 'MM/dd/yy') then Crgreen else Crwhite

It takes the formula but doesnot test the logic as it colors the dates that are greater than printdate.

Any clue if there is anything wrong in the formula ?

 

[nb4884]

Also when i didnt have the isnull formula for date and my background color was

if Maximum(@date, query1)< printdate then Crgreen else Crwhite

this worked very well and also tested the color logic.

 

[IanWaterman]

Use two date formula, one using the original set up for colouring, and the other for display purposes.

ie you place my formula (@datetxt) on design and then format it to chnage colur using your original formula (@date).

Ian

 

[nb4884]

I am sorry ,I did not understand.

Here is what my formuae looks like:

I have placed @date in details and then summarized on Max. (Max @date is in group footer now)

@date
If isnull(datefield) then ' ' else
totext(datefield, 'MM/dd/yyyy')

Now i have to display the summarized dates which is a text now based on colors.

Here is my backfround color formula

if Max(@date, query1) < totext(printdate, 'MM/dd/yyyy') then Crgreen else Crwhite.

This formula however doesnt calculate the date logic . Itried to test it by just displaying year yyyy and it works , as soon as month or date comes the formula it does not color based on th econdition.

I dint get your response on 2 formula.

Thanks

 

[nb4884]

Thanks Ian, the 2 formula idea worked