Some calculations that use functions like CCur and Round on null
values, may look and work good in an Microsoft Access query. But when they are queried
to make a new table with the DAO engine, it will halt with the this error. You must
check all your query calculations for any functions that do not work well
with conversion type functions (i.e. CDbl, CCur, ...)..
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 ) )
To:
CCur( Round( [Backlog] * IIf( IsNull( [UnitCost] ) Or [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:
IIF( [TOTAL Retail] = 0 , 0 , [TOTAL Profit] /
[TOTAL Retail] )
with a Show As type of 'Group Calc'.
You must have group fields with these field labels or aliases already defined in
the same report.
Example:
Field Definition Show
As Default Field Alias Retail
Total
TOTAL Retail
Profit
Total
TOTAL Profit
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:
Alpha Numeric (String) Fields: '' (two single
quotes = a blank or empty string)
Date
Fields:
1/1/1800
Numeric
Fields:
-0.0000000009
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:
IIf( Part 1 , Part 2 , Part 3 )
If Part 1 is true then do Part 2 , If Part 1 is false then do Part 3
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:
If the field is a text field, then you must enter the field description
from: CustID
to: [CustID] & ''
Replace CustID with the field you are working with.
Important Note: The '' in the example above are two single
quotes and NOT one double quote.
Date Fields:
If the field is a date field, then you must enter the field description
from: OrderDate
to: IIf( IsNull( [OrderDate] ) , #1/1/1800# , [OrderDate] ) 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
from: OrderDate
to: Format( [OrderDate] ) , 'yyyy/mm/dd' )
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. change from: 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. Important Note: The '' in the example above are two single
quotes and NOT one double quote.
Number Fields:
If the field is a number field, then you must enter the field description
from: WS#
to: IIf( IsNull( [WS#] ) , -0.0000000009 , [WS#] )
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 second common problem is with calculated field descriptions that cause an
error when dividing by zero.
The corrective action is to enter the field description
from: [Field1] / [Field2]
to: IIf( [Field2] = 0 , 0 , [Field1] / [Field2] )
Replace Field1 and Field2 with the fields you are working with.
Please fill out the form below and we'll be glad to reply (usually within 24 hours).
We honor your privacy and will not give or sell your information to anyone else.
