Problem with Exporting option.

[perezgh]

Hello all, I'm using CR 9.0. I'm trying to export a crystal report to ODBC SQL, the particular field that is giving me a problem is a ENDDATE field. This field can be null or have a value. I execute the export and all is fine until it encounters Null data in that field, after that all data in ENDDATE field is null.

help would be appreciated.


thanks,

G-

 

[synapsevampire]

Sounds like a buglet...

Consider writing a formula to make the date set to some reasonable default when null (as in 1/1/1900), which later processes will indentify as null. You might even execute sql against it to convert it afterwards.

-k

 

[perezgh]

Is it possible that there could be a bug that big? I would hope not.

thanks

G-

 

[synapsevampire]

Try switching to OLE DB.

Are you using the CR ODBC for SQL Server?

You might also create a SQL Expression with a CAST.

-k

 

[SurfingGecko]

What happens if you open the report and the Report Option for "Convert NULL field value to default" on then process the export?


Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

 

[perezgh]

SurfingGecko: I did try that with no help.

Synapsevampire: I'm not that proficient with Crystal yet. But i'm lerning it as I go along. Any recomendations on books that may help me. thanks.

Here's what I did. I generated the report, then I sorted the report by ENDDATE (Desending) so all nulls are at the end. So once it encountered the first null it already had loaded/processed all the non-null dates. I know it's not the way to do it, but it works. I'll figure out later how to do it correctly. When I have time to play around with it.

thanks for all the help.

G-

 

[SurfingGecko]

WOW! looks like it might be a 'buglet' as synapsevampire was alluding to. ;-)

I did some testing and sure enough it is a no go for some reason.

Sorting your report as you have done will certainly work provided you do not want any grouping.

I played around with this and found that this formula should do the trick, although it will fill the field with a bogus date (unfortunately with the ODBC export, conditional suppressed fields is not supported so suppressing if the date matches will not work)

if isnull({db.field}) then datetime(2009,1,1,1,1,1) else datetime({db.field})

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

 

[perezgh]

SurfingGecko: I'm doing the Grouping later. What a bug huh!! Anyway, i'm going to try your suggestion, but i'll convert the date to 00/00/0000 can I? We'll see.

thanks

G-

 

[SurfingGecko]

No doubt...Give it a try with the 0's, but I am not sure if this will work. I was testing against an Access database and it was putting in some crazy dates like 1/1/1851 when I tried with 0,0,0,0,0,0 so I went with a nutty year like 2099.

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com