An I.T. Problem Shared . . .

A blog of problems . . . and solutions

Access 97 Date Format

OK so in 2010 we shouldn’t still be dealing with Access 97 databases but in the real world some people still have them!

So a client tells me their Access 97 database form is showing them the wrong data for the date range they enter. The form is opened from a command button on another form where the user enters a date and this date is used as the link criteria to filter the main form.

I pulled up the query the form is using and it works fine when I enter a date. I get the date entered to pop up in a message box to check that it looks OK and it does then when I use the command button the data returned is wrong for the date entered.

I check the dates in the table are the correct format and that the regional settings of the PC are correct (set to UK).

After a little Googling it appears that in Access 97 VBA assumes automatically that all dates are in US #mm/dd/yyyy# format – which explains why the query worked fine but the command button (which filtered the form using VBA) didn’t work as it was converting the UK date entered to a US date and so returning the wrong records.

Luckily the fix wasn’t too hard. Use the format function to return the correct date if handling dates in VBA:

'Code Courtesy of
'Joe Foster
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

and then

SQL = SQL & "When >= " & Format$(Now, JetDateTimeFmt)

Many thanks to for that one!

iPhone contacts lost after IOS 4.1 update

I upgraded my iPhone 3GS to IOS 4.1 yesterday and as with the last update it lost all my contacts.

I use Google Apps for my contacts, mail and calendars and sync them with my iPhone (by setting up an Exchange account on the iPhone) and as last time the only way to get my contacts back was to delete the Google apps account from the iPhone and set it up again.

Unable to see buttons on a Netbook screen

Netbooks by their nature have small screens, many which have a vertical resolution which is on 600px. The problem with this is that many web pages and some applications have dialog boxes or popups where the buttons you’d like to click (like OK and SAVE!) are often off the bottom of the screen and unreachable.

One solution to this is AltMove which is a program that allows you to press the ALT key and then left click the problem window and move it up until you can see the missing buttons.

Thunderbird Character Encoding

Recently I’ve been receiving email in Mozilla Thunderbird which display odd characters. Changing the character encoding of the individual message solved the problem but only for that message.

Auto detection of the character encoding was enabled but didn’t seem to be working – until I messed around with the settings and did this:

  • Go to Tools > Options > Advanced > Config Editor and make sure that mailnews.force_charset_override is set to false
  • Now right click on your Inbox and choose Edit > Folder Properties and make sure that the checkbox “Apply default to all messages in the folder…” is NOT checked.
Use the Config Editor to ensure that mailnews.force_charset_override is false (its default setting). Also choose Edit – Folder Properties… and ensure that the checkbox “Apply default…” is clear.