US/UK Date Formatting

[bdj]

I am relatively new to Access 2000 but I keep on coming across the same problems with the date formatting. When using SQL or exporting information from a database to a spreadsheet or using VBA it constantly loses the UK formatting.

Can anyone point me in the direction of any resources on the Web where these peculiarities are explained – the more detail the better. I really need to know how to store it in a table (which format)? How to retain the formatting in SQL and in VBA and also how to display it in forms.

I have done an Internet search but not come up with anything really useful and I was hoping that one of the Access gurus would be able to help me.

I realise it is not a problem for all US programmers but it really is a “downer” on this side of the “pond”

Any help would be appreciated

Brian Jermain
Scotland

 

[Vassago]

Have you tried using the format function, like:

format(date, "DD/MM/YY")

That should always work...

Let me know if this is useful or you need more information.

 

[bdj]

US/UK formatting

I wish it were as simple as this.

What I am trying to find out is if you create a table is it beneficial to retain the US format and only use the UK format when you are displaying in a form or report. In this way the base table will always be OK so that when the information is exported to another table or spreadsheet because it comes from the base table it will always be in US format.

I have one particular database that I have been trying to resolve for months - since May 2002??!!
Unfortunately it is rather complicated. The database cannot run across the network for a variety of reasons so it has been set up to run with a master and multiple slaves all operating individually. Each PC has it's own ID which is what controls the import and export routines

The Master database (A) receives information (via and e-mailed file) from databases (B), (C) and (D) in the shape of exported tables which are then imported into the main database (A). The changes are then exported and e-mailed to (B), (C) and (D) to keep them up to date. Unfortunately when the information is exported to a table it is in the UK format but when it gets imported it changes to the US format and of course when it exports the information it then uses the US format which changes all the dates of birth and other date entries and of course it does this with all the other copies

I need to get it fixed in my mind how to control the formatting so as to resolve this problem. the database was written by a guy who was very good at SQL and VBA but not very good at explanations in the routines he has written and with my limited ability in both SQL and VBA I have been unable to resolve this issue. This database is currently running live although the importing and exporting has not been implemented and I am trying to write an update which will fix the problem.

Any help would be appreciated.,

Brian Jermain
Scotland

 

[Rich]

You might like to read Raskew's interesting solution to a similar problem
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=38964

 

[Vassago]

What is the format used in the UK? I was just curious. So many people us so many different formats here it's unbelievable.

 

[bdj]

US / UK Date Formatting

The UK format is dd/mm/yyyy

Unfortunately a date like 3/10/2003 becomes the 10th March 2003 but 31/10/2003 stays as 31st October 2003 because there is no US alternative.

This is what creates the problem

Brian Jermain
Scotland

 

[Pat Hartman]

Let me clarify the date situation a little. The Access Date/Time data type is actually a double precision number. It is NOT a text field. The integer portion of the number represents the number of days since Dec 30, 1899 and the decimal portion represents the time of day. This makes internal sorting and date calculations quite easy for Access.

Now to the confusing part. Unless you specifically format a date field, Access defaults to displaying dates in the format defined by your Windows date format and country settings. It assumes that dates will also be entered in that format but it does try to help you if it thinks that you have made a mistake. The standard US date field order is month, day, year. A large part of the rest of the world uses day, month, year order. So if your date settings are US and you enter 20/10/2, Access will assume that you made a mistake and evaluate the date as Oct 20, 2002 even though standard date order would have been 10/20/2. Access "knows" that 20 can't be a month so it assumes you have made a mistake and treats 20 as a day and as long as the rest of the string makes sense, carries on. Of course with a day value of 12 or less, Access has to rely totally on your windows language setting to interpret the date. So 12/10/2 would be interpreted as Dec 10, 2002 with US language settings and as Oct 12, 2002 with UK language settings.

Now, the problem isn't with Access per se, it is with SQL and even then the problem is limited to string fields. Once a date is in a field of data type = Date/Time, there is NEVER any confusion with processing it because now it is a double precision number as opposed to a string.

The place where you need to be careful with dates is when you are prompting for a date that you want to use as criteria in a query. The date is entered as a string and you will need to get it formatted as mm/dd/yyyy in order for SQL to be able to properly evaluate it.

bdj's import/export problem is probably caused by inconsistant language settings. Make sure that ALL computers using the system are using the same system settings or you're in for a boatload of confusion.

 

[Vassago]

Good explanation Pat!

 

[bdj]

Us/UK Date formatting

Thanks for all your help but it really does not explain why when it is exported from one copy of the database in the correct format it changes the records when it is imported into the master copy of the same database. The tables that are being imported to are all set for UK short date and still the problem persists

All PCs are on a common platform Windows 2000 and Access 2000 set to UK date format.

For those of you who may be interested I have attached a text copy of what I believe is the offending routine. The whole database is over 1.5 meg with no data.

Thanks once again for your help. I certainly am now beginning to understand some of the problems and why I can never seem to resolve them??!!

Brian Jermain
Scotland

 

[Rich]

Try
IIf(IsNull(.Fields("Date")), "Null", "#" & Format(.Fields("Date"),"dd/mm/yyyy") & "#") & ", '" &

 

[Pat Hartman]

I would suggest changing the name of the date field to something other than just date. Date is the name of a VBA function and it will at some point cause you a problem. Also, I'm not sure why you have written all this code. I think the transferText Method would have done this for you with a single line of code once you had exported the file once manually so you could create an export spec.

 

[bdj]

US /UK Date Formatting

Unfortunately I did not write the code. I just inherited the database and have been trying to sort out the problem.

I have never heard of the transfer text methodology but I will certainly investigate and thanks for the advice for the date field - that certainly makes sense to me

Thanks again

Brian Jermain
Scotland

 

[bdj]

US/UK formatting

I have tried Rich's suggestion and it didn't work unfortunately.

However rather than use Format dd/mm/yyyy I decided to go the whole hog and use mm/dd/yyyy in exactly the same way as he described two posts ago and low and behold it works.

Does anyone know why?

I have now used it for the date of birth formatting and modified time and everything seems to work perfectly.

Thanks for all the help

Brian Jermain
Scotland

 

[Rich]

You have to use the US date format when returning / comparing dates in strings