|
|
Error 94 - Invalid Use of Null: Some calculations
that use functions like CCur and Round on null values, may look and work good in
a query. But when they are queried in a make table with DAO the engine will halt
with the error above. You must check all your query calculations for any functions
that do not work well with conversion functions.
We recently changed a field in an Access query to fix the problem:
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 of:
IIF( [TOTAL Retail] = 0 , 0 , [TOTAL Profit]/ [TOTAL Retail] )
with a show as 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.
Nulls in group Show As types are converted to automatically defaulted to values of:
| Alpha Numeric (String) Fields: | '' ( two single quotes = a blank or empty string) |
| Date: | 1/1/1800 |
| Numeric Fields: | 0 |
The Drill-Down grid will NOT link multiple groups on null values. So
we convert null values to a value the grid can link with. 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 3The first common
problem is with null values and field descriptions defined as a Group or Group Add.
Null values do not link properly to sub levels. You must convert the Null value
to another value. The corrective action 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/100# , [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.
or a better method but may be slower on performance is:
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' )
to: Choose( Val( Month( [VenDueDate] ) & '' ) + 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( [OrderDate] ) , '' , 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 will then look
at the first return 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 OrderDate 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 , [WS#] )
Replace WS# with the field you are working with.
Divide by Zero Fields:
The second common problem is with calculated field descriptions that cause an error
when divided 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
Special ! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchase(s) totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected].
Secure Payment Options &
Developers Site
Drill Down Tally (Enterprise
EDITION)
Drill-Down Tally is the best Drill-Down Tally for any business. Just 3 easy
steps: define groups, calculations and details THAT'S IT! It will supercharge
your Microsoft Excel, Microsoft Access, QuickBooks, ACT!, ODBC, csv and text
data files overnight. Gain instant control over your data now with unlimited
on-demand reporting. Any employee can create as summarized or as detailed
reports faster than Crystal Reports. Start your own business, enhance your
current business or gain new clients with Drill-Down Tally. This product gives
you the competitive edge. We have developed and redesigned many databases
generating immediate results for biggest homebuilder and biggest pool builder in
Arizona. As an added bonus, we can supercharge your business relationships
overnight, with our exclusive ad-hoc Drill-Down Tally (Drill-Down Tally 2005)
leaving your competition in the dust. With Drill-Down Tally you can group by
employees, salesman, categories, locations, and/or regions. Utilize your
currency fields as sum total calculations and/or use id fields as count
calculations. Choose any other fields you don't group as details to support your
summarized group calculations. You can have any number of group levels to
drill-down on. Arrange them in any order you like. It's all up to you. You will
have summarized totals and details for every level as you drill-down on each
group. You can filter for records last week, month, and year or do a five-year
trend analysis on your date fields. Filter on any or all fields you need too.
Basically you have an unlimited number of report combinations right now at your
fingertips without the need of a programmer. Stop the guesswork and get accurate
data now
Drill Down Tally (SERVER EDITION)
Drill-Down Tally is the best Drill-Down Tally for any business. Just 3 easy
steps: define groups, calculations and details THAT'S IT! It will supercharge
your QuickBooks, MS Excel, MS Access, ODBC, ACT!, Shop Controller, csv, and text
data files overnight. Gain instant control over your data now with unlimited
on-demand reporting. Create reports as summarized or as detailed as you like by
any employee. Create reports faster than Crystal Reports. (Base price is only 1
license)
Drill Down Tally (EDUCATIONAL EDITION)
Drill-Down Tally is the best Drill-Down Tally for any business. Just 3 easy
steps: define groups, calculations and details THAT'S IT! It will supercharge
your QuickBooks, MS Excel, MS Access, ODBC, ACT!, Shop Controller, csv, and text
data files overnight. Gain instant control over your data now with unlimited
on-demand reporting. Create reports as summarized or as detailed as you like by
any employee. Create reports faster than Crystal Reports. (Base price is only 1
license)
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS
