Quote:
Originally posted by Gary.Lowe
Please, please, please help.
This is doin my head in.
I have a crystal report based on a SQL table. The report is based on Branch number and each section is repeated.
i.e.
Branch Number = 1
Field1 = 23
Field2 = 22
Field3 = 35
Field4 = 25
Total Field = 105
Branch Number = 2
Field1 = 20
Field2 = 15
Field3 = 25
Field4 = 25
Total Field = 85
This is fine everything adds up.
My problem is every so often one of the sections will not add up correctly so instead of the above being 85 the total field will display a lesser figure i.e. 70.
The formula would be as follows:
Total Field = Field1 + Field2 + Field3 + Field4
I can't understand why this is doing this , I have set any nulls to be defaulted to 0 but I know this would not effect this as the database field is set to default to '0' anyway.
Can anybody help.
I can not tell you how much trouble I have had with Crystal Reports, but I have found that often the problems trace back to CR not doing what I would expect it to when it runs into an unplanned event. I would start by looking carefully at the recordsets that the query is returning. If there are joins whose report fields do not have matches, but the recordset still creates an entry (ie invoices with no invoice lines where you are selecting invoices and joining them to the invoice lines) then even though the table defaults to 0 the actual data in the recordset is still null. CR works with the actual data regardless of what the format displays. CR does unexpected things with nulls in formulas. I have had to go to extreme lengths to get some recordsets to report properly. One method that is slow and ugly, but has worked in the past for me when I got tired of fighting CR is to create a table with needed fields and then populate it just before calling CR and delete the data after leaving CR. It is an ugly answer, but it is a good temp fix. (Of course you still have to set the defaults for value fields in the temp Table to non-null values). If you want to test your SQL for null values and there are a lot of records then goto ACCESS and create the appropriate Query and then in the WHERE parameters for the fields in question enter "IS NULL". Execute the Query and if there are any records then it is very likely that this is the root of your problem. Work with the query until it does not return any nulls. (In the Invoice//Invoice Lines example above, Select Invoice lines and then join Invoices to it so that only No invoices will appear in the recordset if there are not any invoice lines. Sounds picky, but it might solve your problem.)