Given the kind of crap we are bound to see and read in the next few weeks, here is an explanation of the real issue:
Pages
Categories
Old Posts
- May 2010 (2)
- March 2010 (1)
- February 2010 (1)
- December 2009 (4)
- November 2009 (1)
- October 2009 (1)
John Cleese on Proportional Representation
Posted in Uncategorized Tagged general election, it's a joke, PR, proportional representation Leave a comment
Capitalise all Words in VBA
As part of an Access data cleanse, I needed to capitalise company’s and people’s names and addresse. So I googled for a regex that would do it. Of course I found plenty that would work very nicely in other languages and on other platforms, but none that worked in VBA. I found that I was not the only person who had faced this problem, but no working solutions. So I have developed my own and present it for you here.
The first problem of course is to break the string up into words. Regex 5.5 will do this very nicely for us. What it will not do is to capitalise the words when it finds them. For this we need to process the found words with vbProperCase.
Here is the complete function for you
Public Function CapitaliseNames(ByVal strIn As String) As String
‘~~~~~
‘Name: CapitaliseNames
‘Version: 1.00
‘Purpose: capitalise the input name – handles hypens and apostrophes
‘
‘Arguments:
‘ strIn = string to capitalise
‘Return:
‘ capitalised string
‘
‘Author: David Soussan
‘Updated: 14/01/2010
‘
‘Comments:
‘ requires reference to VBScript Regular Expression 5.5
‘~~~~~
On Error GoTo ErrorHandler
Dim strReturn As String
Dim oRegEx As RegExp
Dim Matches, Match
Const strPattern As String = “\b(\w+)\b”
‘~~~~~
‘use regex to find all words
‘~~~~~
10:
Set oRegEx = New RegExp
oRegEx.Pattern = strPattern
oRegEx.Global = True
Set Matches = oRegEx.Execute(strIn)
‘~~~~~
‘capitalise each word found
‘~~~~~
20:
For Each Match In Matches
strIn = Replace(strIn, Match.Value, StrConv(Match.Value, vbProperCase))
Next
‘~~~~~
‘lowercase common terms
‘~~~~~
30:
strIn = Replace(strIn, ” And “, ” and “)
strIn = Replace(strIn, ” Of “, ” of “)
strReturn = strIn
ExitProcedure:
CapitaliseNames = strReturn
Exit Function
ErrorHandler:
strReturn = “”
Select Case iLogError(Err.Description, CODE_MODNAME, “CapitaliseNames()”, Err.Number, Erl)
Case ERR_IGNORE
Resume Next
Case Else
Resume ExitProcedure
End Select
End Function
It does of course require my error handler – so substitute your own there (and sorry about the formatting – can’t seem to get copy/paste working in wordpress 
) And do make sure that you keep the blank before and after the ‘ and ‘ n ‘ or ‘ unless you want to spoil names like Anderson and Oregon 
The Most Truth That I Know
If we could conquer greed then we would conquer need.
This is the most truth that I know.
I only wish it could be so.
Some cool Flash widgets
Just came across some cool Flash widgets on abowman.com, check them out.
Pengiuns: they follow your mouse around.
Fish: click on the pond to feed the fish
[edit]turns out it was the FollowSite plugin that was slowing up my blog – lousy plugin that does not work[/edit]
Posted in Uncategorized Leave a comment
Play a game and donate water
Try it out: Play a game online and do some good while you’re at it.
Posted in Uncategorized Leave a comment
Act on Global Warming
Get active about global warming and the failure of the Copenhagen conference, click the logo and send an email to our ‘leaders’.
Calculate the Running Balance
Another fine use of my sequencing method is to calculate the running balance on a ledger:
SELECT t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, Sum(t2.Total) AS balance
FROM tmpDirLoanData AS t1 INNER JOIN tmpDirLoanData AS t2 ON t1.CompanyID = t2.CompanyID
GROUP BY t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, [t1].[postingdate]>=[t2].[postingdate]
HAVING ([t1].[postingdate]>=[t2].[postingdate])=True;
Here I am using the same method to calculate a Sum instead of a Count – that is the only difference.
PS this is JET sql in case anyone is wondering.
Find user’s most recent post in each thread
One common problem that my sequence method can help to solve is how to display the most recent entry that a user has made in each thread that the user has contributed to:
SELECT * FROM posts WHERE usedid = $userid AND postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE AND sequence = 1)
Notice that I use the reverse sequence here (t1.postid <= t2.postid) so that the posts are numbered in descending order with the most recent = 1.
How to Sequence each Sub-set of Records
I first developed this method in answer to a very specific problem posted on PHP Builder:keep last 5 logs and delete rest. It is an ideal solution to this knotty problem where there are an unknown number of records in each subset.
The problem is compounded because records will have been inserted in a more or less random order across the sets. To solve it we have to find the sequence within each sub-set.
Now some DBs such as MS SQL and Oracle have that functionality built in, but others do not. For these we need another approach, and the method is to use a self-join with grouping to generate the sequence number.
SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid >= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE
What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.
To reverse the sequence simply reverse the selection
t1.tableid <= t2.tableid AS flg
So to solve the original problem we just need the reverse sequence which we can then use to delete all records with a sequence greater than 5, neat huh.
DELETE FROM table INNER JOIN
(SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid <= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 USING(userid)
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE) AS q1 USING(tableid)
WHERE sequence > 5
PS this is of course MySQL syntax and many other RDBMS do not support the USING operator for joins
Der!!
Just having a laugh at all the super serious election stuff going on. Anyone would think this was difficult to understand:
Clegg flirts with Cameron – can never lead enough of his party through into the tory lobby to make that work for more than a vote or two.
Meanwhile, Labour dump Brown.
LibDems join with Labour and all the other parties in a government of national unity.
Only I was wrong
Is the Cleggeron yet more of Blaire’s lagacy? The Tories and LibDems spawning their own doppelgängers? Could well be.