My boss is Japanese and has asked that dates be written in the Japanese manner (i.e. year/month/day), but with European numbers. This request only applies to the dates on the tblDates table and not to those on the tblProjects table. It seems to me that the simplest way to do this is to specify a custom format for a Date/Time data-type. The format I wrote is "yy/mm/dd" and the input mask is "00/00/00" Sometimes when I enter the date in the Japanese format, the order is changed. I've attached a sample to illustrate. The date for ProjectID: 1 is autmatically changed whereas the date for ProjectID: 2 remains correct.

Can somone figure out what I'm doing wrong. If not, is there an ulterior method?

How you display a date can be controlled through formatting. How a date is stored internally is controlled using the Regional setting of the operating system. You will need to change your Regional settings if you want the dates to behave as expected.

Use a string, instead of a date type.
If your application only works if all users have the same Regional Setting you will have problems.

I tried the string idea, but this is my first time using VBA and I don't think I did it right. In the Immediate Window I typed: ? CDate("52 Feb 04") and came up with a simple number. This is my very first time using VBA. Could you walk me through the process?

Thanks

If you use strings then CDate can not help you, what do you want CDate("2 Feb 04") to do

Store the date as a string in your table, YY-MM-DD

All your tables should represent dates in the same way, using different methods in different tables, will cause great confusion.

I'm sorry, I thought a CDate was the date version of a string. I have gotten to VBA yet. This is my first database. The reason we want two formats for our dates is that my boss is Japanese and he prefers reading dates in the yy.mm.dd format, but in any other report I may produce dates must be in the European format (i.e. dd.mm.yy). To make matters worse all monetary values are to be in US $s. I find it quite ironic that Microsoft, one of the major players in globalization, has not made it software compatable to people who work in the international arena.

ANyway, I will explain to my boss that it is too complicate. Thank you anyway for your time.

It is not too complicated... Just a challenge to over come - I have come across the same problem in the past with queries and US/EU formatting... Add a new module to your database and insert the following code into it:



I think that will be the right way of formatting it for you. If not switch the dd/\mm/\ with mm/\dd/\

to make calls to it for queries or the such you insert SQLDate() as the criteria in the query.

Any hassles give a shout.

Regards,
Brendon Bezuidenhout
Computer2000 Marketing Technical analyst

I was suggesting storing dates as strings, so that date type and CDate are never used.
I always avoid using date type

On your forms/reports you can display the date however you wish.
You could store the date as 3 fields Year, month and day if that makes it easier, and combine the 3 strings when you display them.

This might help if I understand you correctly (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) :

Format: yyyy/mm/dd
Input Mask: 0000/00/00;_

Thank you all for your help with this issue. I've been assigned to another more urgent task and have to put this project aside for a little while.

Thank you again for your input.

Reading through the post I was about to suggest the same thing EFCoins said. Store the date in three seperate fields and then stick them together however you want. Seems like the format function would work just as well. If all else fails try the old Left$, Right$ and Mid$ functions to pull the date apart and then stick it back together however you want.

J