2009-08-27

Microsoft Access 2003 finding the minimum and maximum column values using a VBA function

If you may have to write a query in MS-Access 2003 to get the maximum or minimum of the values of two columns, you could use the iif() expression like this:iif(n1 > n2, n1, n2) or iif(n1 < n2, n1, n2) to obtain the maximum or minimum, respectively. The expression becomes more complicated when you have to compare three columns:

IIf(n1>n2,
  IIf(n1>n3,n1,n3),
  IIf(n2>n3,n2,n3))

Such expressions are hard to get right, and let's not even consider writing one to compare four columns! (In case you were wondering, we can't use Access' max() and min() aggregate functions because they operate on rows.)

Access allows you to call VBA functions in queries, so we can replace the long and cumbersome iif() expression with a call to a custom VBA function. In the rest of this article, we will:

  1. Define a custom VBA function.
  2. Calling a customer VBA function.

Define a custom VBA function

Here's a simple VBA function one to find the maximum of a list of values (just change the name and reverse the comparison to find the minimum):

Function max_value(ParamArray n() As Variant)
  Dim i As Integer
  Dim ret As Variant
  ret = n(0)
  For i = 1 To (UBound(n))
    If ret < n(i) Then ret = n(i)
  Next i
  max_value = ret
End Function

The ParamArray declares n() as a variable argument list (or varargs in C). Using ParamArray means that the function is not restricted to a pre-defined number of parameters, and using a Variant means that the function will work for different types of data, such as numbers and dates.

The body of the function just loops through all the values in the argument list and returns the largest value.

Calling a custom VBA function

If you have a table called Number with fields n1, n2, n3, n4, n5, you can call this function in a query, just like any other built-in function, like this:

SELECT max_value(n1, n2, n3, n4, n5) as max_value, n1, n2, n3, n4, n5 FROM [Number];

2009-08-14

Google Reader and Firefox's maximum popup limit

If you browse using Firefox and use the Google Reader keyboard v shortcut that I wrote in an earlier post, you may find that Firefox will eventually generate this message, Firefox prevented this site from opening a pop-up window, although you have added www.google.com to the Allowed Sites - Pop-ups dialog. This behaviour occurs because Firefox prevents any site from automatically opening too many popups.

According to Lifehacker's Increase Firefox's Maximum Pop-up Count, the number of pop-up windows a site is allowed to show is controlled by the dom.popup_maximum variable. By default, it is set to 20, so to avoid hitting the limit, I increased this value. Of course, now my browser is more vulnerable to a pop-up window attack or runaway script from all sites listed in the Allowed Sites.

Perhaps a better solution is to have a per-site limit?

2009-08-12

Keyboard shortcuts for navigating Google Reader and Gmail

After using any application for a while, I start using some keyboard shortcuts to speed up common operations. Desktop applications usually have keyboard shortcuts while web applications don't; luckily for me, two of my most used Google applications, Reader and Gmail, have keyboard shortcuts. You can find the list of shortcuts easily so rather than going them, I'll just highlight useful keystroke sequences for navigation and suggest mnemonics to remember them.

?

Both applications support ? to show and hide their keyboard shortcut help page.

Reader shortcut sequences

gF and gt
(g)o to items in people you (F)ollow or a (t)ag. Note: an (undocumented) alternative to gt is gl, (g)o to a (l)abel, like Gmail.
nov and pov
browse (n)ext or (p)revious item in a list, (o)pen it and (v)iew the original. Note: if you're using Firefox, you may be prompted to allow the www.google.com domain to open a window or tab.
NOA and POA
browse (N)ext or (P)revious subscription, (O)pen it and mark (A)ll items read. Good for quickly scanning whole subscription (or tag) items. Just remember that operations on subscriptions use capital letters.

Gmail shortcut sequences

gi and gl
(g)o to (i)nbox or a (l)abel
j and k
browse next or previous conversation (or thread) in a label. These are Vi-style keystrokes (Ah, that brings back memories!).
n and p
browse (n)ext or (p)revious message in a conversation (or thread).
u
go (u)p to thread (or conversation) list from reading a message.

Note that Google uses the terms conversation or thread interchangeably in their online help. Unlike Reader, Gmail doesn't have shortcuts for browsing labels (or tags).

So, there's a very short list of keyboard shortcuts for that I've found useful for navigating Google Reader and Gmail; I hope it'd be useful for you too.

2009-08-09

Read 'Times Online' article comments earliest first

The comments appearing after articles in the Times Online are displayed in reverse chronological order (latest first), which I find difficult to read, so I wrote this GreaseMonkey jQuery script to click the Oldest first link after the page is loaded:

// ==UserScript==
// @name         Timesonline.co.uk earliest comment first
// @namespace    kamhungsoh.com
// @description  Show an article's comments, earliest comment first.
// @require      http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js
// @include      http://*.timesonline.co.uk/*
// ==/UserScript==

var evt = document.createEvent('HTMLEvents');
evt.initEvent('click', true, true);

$("a:contains('Oldest first')").each(function() {
  this.dispatchEvent(evt);
});

The script is a little more complicated than I expected. Apparently jQuery cannot send events (see the last response in the thread) that it itself did not bind to objects, so my script has to create a click event object and send it to the link. Otherwise, I would written $("a:contains('Oldest first')").trigger('click');

Also, the script's a little hacky because it will send a click event to all links containing the text 'Oldest first', though it seems pretty unlikely that a page will have more than one link of that type in the first place.

2009-08-05

OpenOffice Calc AutoInput capitalization annoyance

When you enter text in a cell in OpenOffice Calc, you might find that your text is incorrectly capitalized and you cannot correct it! For example, while writing a vocabulary list, I entered 'sun' and it was capitalized as 'Sun' (maybe referring to a certain technology company). If you type Ctrl+Z to undo this capitalization, all the text in the cell is removed, not just the capitalization. It seems that Calc's AutoInput feature is automatically capitalizing text before it is entered (unlike 'AutoCorrect', which makes the correction after the text is entered, so you can undo the capitalization). I don't know how AutoInput differs from AutoCorrect, and I haven't found AutoInput useful so I've disabled it using menu item Tools / Cell Contents / AutoInput.