International Dates in Access SQL (VBA or Direct Query)

After a day of googling, binging, shouting and screaming at an inorganic yet most likely demonic creation that is the PC, I think I've finally managed to sort this baby out.

The Environment

Date in a table is stored using the UK date system, i.e. dd/mm/yyyy. System is also set for the UK date/time and locale.

The Problem

In VBA, I was using Dlookup to retrieve an ID associated to a specific date. It sometimes returned the wrong result (sometimes Null). This was inside a loop and looped through a set of dates inside a table.

Attempted Solutions

At first I thought its a flaw in VBA. So i tried Cdate , CVdate, converting to string, converting to date, comparing strings and so on and so forth. Spent half a day playing with VBA trying to get the right date.

Then (i dont know why i waited this long to try this...live and learn!), I decided to try a query in SQL using an explicitly declared date. It worked. So back to VBA.

After another hour or so...I had narrowed it down to errors starting when the month changed a carrying on thereafter. Again I decided to check SQL and Voila! Wrong Result. Changing to the american date system gave the right result. So obviously even though my system settings and everything else said UK date format not to mention access using the right format everywhere else, when it came to an SQL query involving dates, it only used the american method.

Then came further web trawling to try and fix this issue...

Working Solution

The following method worked for me.

Suppose you have the following code:

...

dim tmpdate as date 'this is the date I have

dim tmpstr as string 'this is the string i want a value returned to

tmpdate ="15/09/2007" 'The code actually looked it up and got the right answer

tmpstr = Dlookup("field","tablename","[DatetoUse]=" & tmpdate)

...

It gave tmpstr = Null even though it shouldnt. However if I modified the criteria bit in Dlookup using the Format() function, it worked ok. My new line was

tmpstr = Dlookup("field","tablename","[DatetoUse]=" & Format(tmpdate,"mm/dd/yyyy"))

Bizzare. At least now it works correctly!

I am Browser King

The ubiquitous Internet Browser. In a future where SaaS would be fairly standard, the focus will be as much on the browsers as the OS. Its probably the lightest method to get true blue cross platform compatibility. As browsers go, I think this is the first time I am unable to pick one to use as standard.

Around '96-97, in the early days of the internet, Netscape would rule the roost for me. I still remember getting a copy of Netscape Navigator 3 Gold Edition with my new PC. The geek in me was proud to possess the ultimate browsing tool. Nevermind the 19.2Kbps internal modem that ensured loading times far exceeded the life of the universe....

Netscape and ICQ pretty much dominated my early Internet excursions. Of course there was the occassional experiment with Opera or Internet Explorer, one always went back to Netscape. Till it kicked the bucket sometime around version 4.

With microsoft settling its anti trust issues long enough to launch a seemingly better browser in '99 and IE 5 was good. Nothing exceptional but completely functional. Thats how it remained till the end of the IE 6 series. Tons of security holes, minor issues, slow load up times but it worked with every website. When in doubt, IE it.

Then came the launch of Mozilla (later Firefox). Here was a browser loudly proclaimed as the spiritual successor to Netscape. An alternative to the microsoft anathema. It was buggy, and ate my ram for breakfast. And wasnt compatible with all sites (though more compatible with the web standards to be fair to it). Yet for a long while this remained my standard tool of choice. IE 5/6 was back up. Firefox was the main browser. Then IE 7 came out. More Secure. Similar Features. A pile of rubbish along side that other monstrosity, Vista.

Two softwares of the Apocalypse. XP was rock solid. Stable, fast and easy to use. With a few tweaks, its security flaws were easily fixed. The OS to end all OSes. Then came Vista. Irritating. Slow. Irritating. Compatibility Issues. Irritating. The software equivalent of a nagging wife. Probably well meaning but constantly warning me that its risky to run my game. Or to let it access the internet. or to download that file. Happy to say Windows 7 rocks. Maybe its a MS thing. Windows 95 was irritating. 98 good. Me shite. XP Good. Vista Shite. 7 good. expect the 7's successor to be rubbish at this rate...but i digress.

IE 7 was rubbish. Firefox entrenched itself on my PC. Till Chrome reared its head last year. Fast. Simple. Clever. Didnt eat my memory and fairly stable. Daily Browsing on chrome. something didnt work, use Firefox else IE as last resort. And so it went...till a few months ago and IE 8. Also fast and safe and a few clever features. Nothing exceptional but then one would be asking too much if it did. I thought twice about a website if it meant loading IE 7. IE 8? no problemo. But heres the catch. its still number 2. 2? Wasnt Firefox 2? Alas , no! though there is little to choose between the two, IE is a teeny bit quicker to load and doesnt eat my memory so it took 2nd place over firefox. That said, there is very little to choose between all three of them.

Now comes Opera 10. And after 20 minutes of use it feels excellent. I love the sessions feature. Something akin to what I hacked into my chrome. I always find myself loading a similar set of pages depending on what I am doing and have been loading it up manually using an external file. Now though, with sessions built into Opera, methinks thats in the past. It really is something that should've been done ever since tabbed browsing came about. Now its here. Expect the others to incorporate it soon. Gestures and other bits are all built into it as well but i havent really tried it yet. Loved the gestures addon in firefox when i used to use it. havent really used it anywhere since but something tells me opera and chrome will become dominant. FF is like the iPhone. every1 has one and tons of apps. IE is like, well, any windows mobile. functional and does everything. Not as cool. Chrome is the young pretender. People use it and its slick. and now theres Opera. Something touted to do all of the above. As is the case with many things, commercial success and critical success dont necessarily see eye to eye.

As of now, I dont use any one exclusively. We are creatures of habit and once we get used to a browser we dont really change. Thing is though, not one browser offers all the features I want. Firefox still has memory issues. IE is still a bit bland. Chrome still has minor website compatibility issues. And i havent tried out opera fully yet but I like what I see so far. The key point though still remains the commercial success of a browser as it will define how useful it will be tomorrow. Netscape ensured Javascript was developed. IE ensured ActiveX acceptance. Mozilla/FF introduced a lot of new approaches to browsing. All possible because of their dominance in the marketplace ensured others to adopt their ideas. Much like how Microsoft ensured software developed at the pace it did (we do have the business nous of ol' Billy Gates to thank for that).

The Google Brandname will ensure Chromes success. Microsofts power (not to mention the improved user experience) will keep IE strong. Firefox...heres something which will suffer a bit at the hands of chrome. Which leaves us with Opera. Will it garner critical acclaim? Will it achieve commercial success? Is there room for it at the big boys table? answers on a postcard...