 |
GreatManuals.com |
|
|
Corrective Actions Error 94 - Invalid Use of Null:
We have changed a calculated field in an Access query to fix the problem that worked ok in the Microsoft Access application, but did not work when creating a new table to work with Drill-Down Tally.: So the query was changed to include IsNull( [UnitCost] ) Or [UnitCost] < 0 instead of just [UnitCost] < 0 like we did in the example below. From: CCur( Round( [Backlog] * IIf( [UnitCost] < 0 , 0 , [UnitCost] ) , 2 ) ) Error 3061 - Misspelled field reference(s): If you have a field description like this to avoid a divide by zero error: Example: Now the above 'Group Calc' can find the fields to base its calculation on. There are two common shortcomings that can be corrected with an additional function. Null values in group 'Show As' types are automatically converted to default values of: The Drill-Down grid will NOT link multiple groups on null values. So we convert null values to a value the grid will link with. Although these values show in the grid, they will be converted to blank values on the printed reports. Plus we now have a popup wizard to help you find and create the corrective formulas for you when these situations arise. Prior to version 2007, you had to find and create these formulas yourself. If these default values are NOT sufficient, then read the following instructions to help guide you. The function implemented to enhance the default values is the IIf function. The IIf function has three parts to it and works as follows: The first common problem with null values and field descriptions defined with a Show As type of 'Group' or 'Group Add'. Null values do not link properly to sub levels so you can drill down on them. You must convert these Null values to another value. The corrective action is as follows: Alpha Numeric (String) Fields: Replace CustID with the field you are working with. Date Fields: If the field is a date field, then you must enter the field description Important Note: The Null date or dates prior to 1/1/1920 will be displayed as 1/1/1920. 1/1/1920 is the earliest date the program can work with. When Drill-Down Tally encounters a date of 1/1/1800 it will blank it out on your printed reports. If you need to work with dates before 1/1/1920 see the FORMAT function below to find a solution to this issue. Another method is to convert your dates to a string, but the performance may be slower. Doing this means you will have to put your year first so you date field can sort properly. To do this you would convert your date field Replace OrderDate with the field you are working with. This solution corrects both the null problem and the dates before 1/1/1920. Because it changes the data type from a date to a string. That's why we need to format the date field 'yyyy/mm/dd' so that the new string field will sort properly. Another common problem is when using a function on a date field that is null. to: Choose( Val( Month( [Order Date] ) & '' ) + 1 , '', '1st Qtr', '1st Qtr', '1st Qtr', '2nd Qtr', '2nd Qtr', '2nd Qtr', '3rd Qtr', '3rd Qtr', '3rd Qtr', '4th Qtr', '4th Qtr', '4th Qtr' ) or to: IIf( IsNull( [Order Date] ) , '' , Choose( Month( [Order Date] ), '1st Qtr', '1st Qtr', '1st Qtr', '2nd Qtr', '2nd Qtr', '2nd Qtr', '3rd Qtr', '3rd Qtr', '3rd Qtr', '4th Qtr', '4th Qtr', '4th Qtr' ) ) Because the MONTH function can not return a value of 1 through 12 on a null date field, the CHOOSE function will cause an error. So you must convert the Null value into a empty string '' by one of the two methods above. Either by preceding the CHOOSE function with the IIF function or by using the VAL function to convert the empty string to 0 and then adding 1. This way the function will look at the first returned position of the CHOOSE function. This may seem like a lot or work, but it can also help show you how to get creative by dreaming up new fields from existing fields in your database. Replace Order Date with the field you are working with. Number Fields: If the field is a number field, then you must enter the field description Replace WS# with the field you are working with. Prior to version 2007, zero was used instead of - 0.0000000009. - 0.0000000009 is now used instead of zero to differentiate between values that are non-null in your data files. Usually without any formatting the number - 0.0000000009 will show up as (.00) in the grid. This way you these numbers will not be grouped in with values that do contain a zero value. Important Note: When Drill-Down Tally encounters a number of -0.0000000009 it will blank it out on your printed reports. It is value negative nine zeros and a nine. Divide by Zero Fields: The corrective action is to enter the field description Replace Field1 and Field2 with the fields you are working with.
|
