Monthly Archives: April 2014

Number Formats

Are you tired of trying to figure out if 14258769 is 1.4 million or 14 million? ACL, as a default, does not format your numbers in any convenient way. Thankfully, there is a quick SET command that will fix that.

SET PICTURE "format"

Where format is your choice of formatting (duh). A few suggestions, “(9,999,999.99)” gets you negatives in parenthesis. “-9,999,999.99” will produce negatives with a dash/minus sign. And all numbers will have two decimals and commas.

Simple as that. Now you can read your numbers without having to think too much.

Using Notepad++ to Search Projects

When you need to search across multiple scripts in an ACL project, the process of using Find/Replace can get tiresome. Because ACL projects are just text files, they can be opened up in a program like Notepad++. Notepad++ has some great search features, which can track down the variables or tables you’re looking for. The table name will appear next to the ^BATCH notation. You will also see at the top of the ACL file the table layouts. I wouldn’t mess with this stuff, but if you’re feeling adventurous, go for it!

As your scripts grow more modular, this should help ease the fear of finding that one variable that keeps wrecking everything. Good luck!

Text Editors for ACL Scripts

We’ll take a short break from scripts and talk about tools to work on the scripts. I’ve come to love Notepad++ for copying and pasting scripts (or sections of scripts I reuse). A cool feature is the User Defined Language (UDL) that allows to to define your own code coloring.

Thankfully someone else has taken the time to program in the ACL color coding.

First, if you haven’t gotten Notepad++, you can download it here. Once you’re done with that, you can get the ACL UDL here.


Beware Excel Cell Formats

Everyone knows them: General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific, Text. The wonderful Excel cell format options. Did you know this can have unintended consequences on your Excel import?

I ran into an issue a few weeks ago where the account number looked something like 1000-00-040. It looked like that in Excel, but not in ACL. I’ll admit, I was a bit confused at first. The Data Definition Wizard is just supposed to work, right? Wrong. Excel pulled a fast one. The client’s system was creating a customer format that took a number and added the dashes in. Because we join back to the trial balance, I needed the format to be in ACL as well. So I did what anyone else would do, import as a character, then hack it back together with a few SUBSTR()'s and "-".

It wasn’t pretty, but it got the job done. Bottom line, beware the Excel cell formats!