Posts for 04/2010

28 April 2010 - 15:23

Error handling and debugging in VBA

If you're after some good explanation and highlights in terms of error handling and debugging with VBA, I would suggest these:

(0) Comments ·
15 April 2010 - 15:21

Commands from prompt, batch files and backups

I was asked recently to do a course on DOS-commands and on RUN-commands, i.e. commands you can type at the run prompt.

And although I scoped it somewhat to the most used and/or basic ones, there are people coming up with huge lists of them:

This course really felt like 'closing a cycle', as my very first IT manual ever was a DOS manual which came along with a Commodore PC - one without even a harddisk, that was, back in 1986.
And speaking of DOS commands of course brings us to batch files.
If ever you want to try some:

Actually I'm using a batch files for my daily backups to external harddisks, with log files on top of it.... All thanks to good old DOS and a double click of a shortcut on my desktop :-)
Here's what I use:

set LogPath=e:\Backups\
set LogFileExt=.txt
set LogFileName=Backup%LogFileExt%
::use set MyLogFile=%date:~4% instead to remove the day of the week
::set MyLogFile=%date%
set MyLogFile=%date%
set MyLogFile=%MyLogFile:/=-%
set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
If NOT Exist "%MyLogFile%" goto:noseparator
echo.%Date% >>"%MyLogFile%"
echo.%Time% >>"%MyLogFile%"

xcopy d:\data g:\data /d/s/c/h/r/y/f/k >>"%MyLogFile%"
xcopy d:\2013Website g:\2013Website /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy d:\Graphics g:\Graphics /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"
xcopy d:\Images g:\Images /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"

xcopy e:\Audio h:\Audio /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\Video h:\Video /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\VideoAudioTraining h:\VideoAudioTraining /d/s/c/h/r/y/f/k  >>"%MyLogFile%"

(2) Comments ·
13 April 2010 - 10:21

Excel Pivot Tables unplugged: hide items in a filter combo

Excel pivot tables are just a fantastic tool. And you can even boost it in various ways.
Just some creative ideas I encountered while working on a problem with hiding items in a filter.

Sometimes you just need to think out of the box to get where you want to get. And that may mean create an extra column with new data, you can then integrate in your pivot table analysis.
As as sample of this approach: Filter an Excel Pivot table for upcoming dates.

And did you know there are some specific shortcuts and manipulations to sort and hide information in pivottables? You'll find a good article on this at following blog topic.

What I hadn't realised yet is that in pre-2007 versions, there was an option in the 'page field zone' to hide certain items from appearing in the combo box, as you can see in this article on .
Excel 2007 uses a new concept under the name of 'report filter' and as the 'multi-selection' feature has been added, this possibility to remove items from appearing in the combo has disappeared.
So if certain data shouldn't appear, you might have to go back to the source data and hide them there and thus use a 'out of the box' solution.

(0) Comments ·
12 April 2010 - 04:50

Access development: syntax of sql queries in VBA

One of the toughest things to do when coding VBA in Access development projects is coming to terms with SQL queries.
I've found following tips and tricks helpful :

SQL2VBA form

Allen Browne, one of the main authorities on the matter, proposes a form with VBA code which actually does the hard coding into VBA of any SQL string of your choice.
You'll find the code and procedure at his site:

Another thing you might have ran into is using query parameters into SQL strings. Well you can't basically, you must 'transform' them and here is how: If you run into the error "Queries: Too few parameters, expected n" - that's exactly what I'm referring too here :-).

And this might come in handy too: some basic testing for empty recordsets - as when your SQL query doesn't return records in the recordset, you'll off into problem land as well.
I've found this one useful to solve this issue:

(0) Comments ·
10 April 2010 - 05:14
08 April 2010 - 06:54

WP for DOS

short cut to WP DOS

I had never thought to still get involved in a Wordperfect for DOS project in the year 2010 !
And once going into it, I soon discovered there's still a good amount of people using this system for all kind of reasons.
I even discovered two very precious resources in setting up and configuring WP for DOS :

browsing in WP DOS

As it concerned a very specific installation on top of being a DOS program (a Dutch language version with both azerty and US international qwerty keyboards) all kind of issues occurred ...
And getting the keyboards to behave with special accents was probably the most difficult task to tackle.

But end well, all well, and here are some specific links if ever it help someone out too:

typing in WP DOS
(0) Comments ·
03 April 2010 - 07:14

Interesting code resources during recent Access Projects

Doing lots of database development in Access brought up all kind of interesting insights and links.
Just a selection of some of these encountered goodies :

(0) Comments ·
02 April 2010 - 16:36

Random values between two figures in Access

Ingenious all those solution maths-people come up with.
Take the problem to find a random value between two integers.

Well you take the interval between the two figures you have in mind, add one, then multiply with a random value and add the minimum of the figures you had in mind. Just rendering it as an integer and you're done ! If that sounds too much like maths.... well ... it is :-)

Given that the random function in Access is rnd() ( you obtain a decimal between 0 and 1) you get something like this to obtain a number between 10 and 100

Int ((100 - 10 + 1) * Rnd + 10)
For a full explanation go to
(0) Comments ·
Page 1 of 1 pages