Zero detected as Null in Crystal Report
-
I am currently writing a WinForms application in C# with a Microsoft Access database backend and using Crystal Reports for reporting. One table in the database contains a numeric field which can allow any numeric value including zero, but is can also be Null. Currently when I display a report any field that contains a zero value is displayed as empty. I have played around with the "Show zero as" setting but it is either blank for both zero and Null values or it shows "0" for both zero and Null values. I would like to display "0" for zero values and "" for Null values. Any Crystal Reports gurus out there?
-
I am currently writing a WinForms application in C# with a Microsoft Access database backend and using Crystal Reports for reporting. One table in the database contains a numeric field which can allow any numeric value including zero, but is can also be Null. Currently when I display a report any field that contains a zero value is displayed as empty. I have played around with the "Show zero as" setting but it is either blank for both zero and Null values or it shows "0" for both zero and Null values. I would like to display "0" for zero values and "" for Null values. Any Crystal Reports gurus out there?
-
Been a while since I have used crystal reports, you will need to use a formula field. In your formula, it is also important to test IsNull first. Crystal Syntext below,
If IsNull({Table.Field})
" "
Else
{Table.Field}Hope this helps, Regards, Tarco
I tried this however the syntax has to be: If IsNull({Table.Field}) " " Else totext({Table.Field}) Even when I do this it displays "0". For some reason CR is converting all null values to zeros.
-
I tried this however the syntax has to be: If IsNull({Table.Field}) " " Else totext({Table.Field}) Even when I do this it displays "0". For some reason CR is converting all null values to zeros.
-
I'm using c# code to connect to the database as follows: string sql = null; OleDbConnection cnn = new OleDbConnection(Global.connectionStringAccess); cnn.Open(); sql = "SELECT * FROM Register WHERE ItemNo = '" + Global.CurrentItem + "'"; OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, cnn); DataSet ds = new DataSet(); dscmd.Fill(ds, "Register"); cnn.Close(); string rptName = rptPath + "\\rptWorksheet.rpt"; cryRpt.Load(rptName); cryRpt.SetDataSource(ds.Tables["Register"]); crystalReportViewer1.ReportSource = cryRpt; crystalReportViewer1.Refresh();
-
I am currently writing a WinForms application in C# with a Microsoft Access database backend and using Crystal Reports for reporting. One table in the database contains a numeric field which can allow any numeric value including zero, but is can also be Null. Currently when I display a report any field that contains a zero value is displayed as empty. I have played around with the "Show zero as" setting but it is either blank for both zero and Null values or it shows "0" for both zero and Null values. I would like to display "0" for zero values and "" for Null values. Any Crystal Reports gurus out there?
Don't do anything complex in Crystal. Use it as a rendering engine, just allow it to do the "pretty" stuff, like grouping, totals, bolding etc. It's great for that. Use your database to create the output values you want. In your case maybe look at creating a view and using that as your source.
// TODO: Insert something here
Top ten reasons why I'm lazy 1.
-
I am currently writing a WinForms application in C# with a Microsoft Access database backend and using Crystal Reports for reporting. One table in the database contains a numeric field which can allow any numeric value including zero, but is can also be Null. Currently when I display a report any field that contains a zero value is displayed as empty. I have played around with the "Show zero as" setting but it is either blank for both zero and Null values or it shows "0" for both zero and Null values. I would like to display "0" for zero values and "" for Null values. Any Crystal Reports gurus out there?
Is there a specific requirement to use MS Access as your database? Access really should only be used by itself, if used at all. SQL Server Developer/Express is free: SQL Server Downloads | Microsoft[^] I know this does not answer your original question about zeros/nulls w/Crystal Reports, but I could not overlook an opportunity to "try" and convince someone not to use MS Access....for anything, ever. My recommendation is to port over the general table schema and data from MS Access into an SQL Server database, then you are only stuck with the nightmare that is Crystal reports. :laugh:
-
I'm using c# code to connect to the database as follows: string sql = null; OleDbConnection cnn = new OleDbConnection(Global.connectionStringAccess); cnn.Open(); sql = "SELECT * FROM Register WHERE ItemNo = '" + Global.CurrentItem + "'"; OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, cnn); DataSet ds = new DataSet(); dscmd.Fill(ds, "Register"); cnn.Close(); string rptName = rptPath + "\\rptWorksheet.rpt"; cryRpt.Load(rptName); cryRpt.SetDataSource(ds.Tables["Register"]); crystalReportViewer1.ReportSource = cryRpt; crystalReportViewer1.Refresh();
Don't "SELECT *". Select what you need and CASE the data / columns you want to convert. [tsql - T-SQL CASE Clause: How to specify WHEN NULL - Stack Overflow](https://stackoverflow.com/questions/3237646/t-sql-case-clause-how-to-specify-when-null) [What is the equivalent of Select Case in Access SQL? - Stack Overflow](https://stackoverflow.com/questions/15774078/what-is-the-equivalent-of-select-case-in-access-sql)
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
Don't do anything complex in Crystal. Use it as a rendering engine, just allow it to do the "pretty" stuff, like grouping, totals, bolding etc. It's great for that. Use your database to create the output values you want. In your case maybe look at creating a view and using that as your source.
// TODO: Insert something here
Top ten reasons why I'm lazy 1.
OK, I think I've found a workaround to fix the problem. I modified the SQL SELECT statement using the ISNULL function to replace Null values with a large negative number (NOTE: users are limited to entering only positive numbers or small negative numbers in these fields. I then used CR conditional formatting to make these large negative numbers invisible (white foreground/background). I know its crude but it works.
-
OK, I think I've found a workaround to fix the problem. I modified the SQL SELECT statement using the ISNULL function to replace Null values with a large negative number (NOTE: users are limited to entering only positive numbers or small negative numbers in these fields. I then used CR conditional formatting to make these large negative numbers invisible (white foreground/background). I know its crude but it works.
Not really a good idea. If someone prints on a different shade of paper. Or even viewing on the screen and the user has changed their colour settings. Use the formula that you created for the conditional formatting and put it into your select statement.
// TODO: Insert something here
Top ten reasons why I'm lazy 1.