Software Search, Categories and Specials Categories: Excel Add-ins / Excel Templates
Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download

 

AnalyserXL - Download

 

DownloaderXL - Download

 

Smart VBA - Download

 

TraderXL - Download

Drill-Down Tally Common Functionality Corrections

 

Microsoft Excel - From Beginner to Expert in 6 Hours

Reports QuickBooks-Reports ACT-Reports Excel/Access/ODB/ Text/CSV Data Files.

Drill-Down Tally Drill-Down Tally Start

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

Drill-Down Tally Start

Drill-Down Tally FAQ | Drill-Down Tally Screen Shot | Common Functionality Corrections | Customer Testimonials | Drill-down Tally tour

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

EDITIONS & Purchase: SYSTEM REQUIREMENTS: Windows 95 or above

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

Microsoft Excel - From Beginner to Expert in 6 Hours

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