International Date Format Clarification

[Ian Baker]

Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app. We are
now getting a lot of US clients and they are finding problems with the date
format from our dd/mm/yy to their mm/dd/yy. So we are having to rework the
app to accomadate this dare I say strange logic and want to clarify that we
doing the right thing.

1. None of the current table fields that are date/time have any formatting
set at the table level so I presume we don't have to touch them (i.e. what
comes out of the query/form goes into the table)?
2. None of our code uses "#[value]#" so I presume we are ok there?
3. We use Date() and Now() extensively so I presume we don't have to touch
these as they will pick up the Windows system date format?
4. We have form text boxes that have a default value eg
=Format(Now(),"d/m/yyyy h:nn ampm") and a format of dd/mm/yyyy h:nn ampm.
These would have to be changed but how can we still keep the format other
then switching the d/m to m/d?
5. In our code when we have Format(Now, "dd/mm/yyyy h:mm ampm") these would
also have to be changed also?
6. When we have date calculations in our code as long as we don't stipulate
a hard format like dd/mm/yy and instead use a soft format like ShortDate or
MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume these are
also ok?

Basically it seems that we only need to change hard formats into soft
formats. For example where ever we have a hard format of "dd/mm/yyyy h:mm"
we need to change it to something like "General Date" - Is that right?

But also how do you do that without it displaying seconds?

 

[Graham R Seach]

Ian,

1. Correct.
2. Correct.
3. Correct.
4. You have to remove Format(). Be sure to check all queries for instances
of Format, and remove any found.
5. Correct.
6.Correct.
7. Correct.

There may be functions that use Left(), Mid() or Right() against dates.
Check for these too. Lastly, check for control validations and conditional
formatting.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Ian Baker]

oops just noticed the date of the post above - sorry i was testing US format
dates and changed the system date to better test as 11/3 or 3/11 could
either mean 11 Mar or 3 Nov so I changed it to 18 Mar - once again my
apologies

--
Regards
Ian
-

Hi Graham
Thanks for that - just wanted to check. It seems then that we are unable to
stop the display of seconds (unleass we create a specific function) if we
need to use the soft Access formats like General Date to accomadate the US
way - just means we have to make some text boxes bigger.

Incidentally Graham, I was talking to you at the Melb launch of Office 2003
where you gave your talk (and I won the flight sim game) and you mentioned
you were going to have a look at InfoPath. Have you had a chance to as I am
interested in your thoughts vs Access?

--
Regards
Ian
-

Ian,

1. Correct.
2. Correct.
3. Correct.
4. You have to remove Format(). Be sure to check all queries for instances
of Format, and remove any found.
5. Correct.
6.Correct.
7. Correct.

There may be functions that use Left(), Mid() or Right() against dates.
Check for these too. Lastly, check for control validations and conditional
formatting.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app.

We
are

now getting a lot of US clients and they are finding problems with the date
format from our dd/mm/yy to their mm/dd/yy. So we are having to rework the
app to accomadate this dare I say strange logic and want to clarify

that
we

doing the right thing.

1. None of the current table fields that are date/time have any formatting
set at the table level so I presume we don't have to touch them (i.e. what
comes out of the query/form goes into the table)?
2. None of our code uses "#[value]#" so I presume we are ok there?
3. We use Date() and Now() extensively so I presume we don't have to touch
these as they will pick up the Windows system date format?
4. We have form text boxes that have a default value eg
=Format(Now(),"d/m/yyyy h:nn ampm") and a format of dd/mm/yyyy h:nn ampm.
These would have to be changed but how can we still keep the format other
then switching the d/m to m/d?
5. In our code when we have Format(Now, "dd/mm/yyyy h:mm ampm") these would
also have to be changed also?
6. When we have date calculations in our code as long as we don't stipulate
a hard format like dd/mm/yy and instead use a soft format like

ShortDate
or

MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume

these
are

also ok?

Basically it seems that we only need to change hard formats into soft
formats. For example where ever we have a hard format of "dd/mm/yyyy h:mm"
we need to change it to something like "General Date" - Is that right?

But also how do you do that without it displaying seconds?

 

[Graham R Seach]

Ian,

Yes, I do remember you. I hope you enjoy Flight Sim as much as I do!

No, what with work, doing a Masters degree, finishing off a book, plus
family and newsgroup commitments, I haven't had much time to check out any
software.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi Graham
Thanks for that - just wanted to check. It seems then that we are unable to
stop the display of seconds (unleass we create a specific function) if we
need to use the soft Access formats like General Date to accomadate the US
way - just means we have to make some text boxes bigger.

Incidentally Graham, I was talking to you at the Melb launch of Office 2003
where you gave your talk (and I won the flight sim game) and you mentioned
you were going to have a look at InfoPath. Have you had a chance to as I am
interested in your thoughts vs Access?

--
Regards
Ian
-

Ian,

1. Correct.
2. Correct.
3. Correct.
4. You have to remove Format(). Be sure to check all queries for instances
of Format, and remove any found.
5. Correct.
6.Correct.
7. Correct.

There may be functions that use Left(), Mid() or Right() against dates.
Check for these too. Lastly, check for control validations and conditional
formatting.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app.

We
are

now getting a lot of US clients and they are finding problems with the date
format from our dd/mm/yy to their mm/dd/yy. So we are having to rework the
app to accomadate this dare I say strange logic and want to clarify

that
we

doing the right thing.

1. None of the current table fields that are date/time have any formatting
set at the table level so I presume we don't have to touch them (i.e. what
comes out of the query/form goes into the table)?
2. None of our code uses "#[value]#" so I presume we are ok there?
3. We use Date() and Now() extensively so I presume we don't have to touch
these as they will pick up the Windows system date format?
4. We have form text boxes that have a default value eg
=Format(Now(),"d/m/yyyy h:nn ampm") and a format of dd/mm/yyyy h:nn ampm.
These would have to be changed but how can we still keep the format other
then switching the d/m to m/d?
5. In our code when we have Format(Now, "dd/mm/yyyy h:mm ampm") these would
also have to be changed also?
6. When we have date calculations in our code as long as we don't stipulate
a hard format like dd/mm/yy and instead use a soft format like

ShortDate
or

MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume

these
are

also ok?

Basically it seems that we only need to change hard formats into soft
formats. For example where ever we have a hard format of "dd/mm/yyyy h:mm"
we need to change it to something like "General Date" - Is that right?

But also how do you do that without it displaying seconds?

 

[TC]

Ian

Without reading through your whole list, here's what I've done, in
anticipation of exactly that problem.

(1) I let the users >enter< dates in whatever format they want.

(2) I always check that the entered date does not have any time component.
This is for two reasons. First, my app never uses times. Second, try
entering 1.2.3 (in Australia), and see what you get. Oops!!

(3) Each relevant control has a format which causes the entered date to be
redisplayed in the following, unambiguous format: 23-Jan-2004 or
Jan-23-2004.

(4) I have a common procedure SetDateFormat <control>, which sets the format
of the specified control to one of those two formats, depending on the
user's locale. The Open event of each form & report calls that procedure for
each relevant control.

By this means, my app should work in either locale, without any changes, as
far as data entry & display is concerned.

HTH,
TC

 

[Ian Baker]

Hi Graham
Thanks for that - just wanted to check. It seems then that we are unable to
stop the display of seconds (unleass we create a specific function) if we
need to use the soft Access formats like General Date to accomadate the US
way - just means we have to make some text boxes bigger.

Incidentally Graham, I was talking to you at the Melb launch of Office 2003
where you gave your talk (and I won the flight sim game) and you mentioned
you were going to have a look at InfoPath. Have you had a chance to as I am
interested in your thoughts vs Access?

--
Regards
Ian
-

Ian,

1. Correct.
2. Correct.
3. Correct.
4. You have to remove Format(). Be sure to check all queries for instances
of Format, and remove any found.
5. Correct.
6.Correct.
7. Correct.

There may be functions that use Left(), Mid() or Right() against dates.
Check for these too. Lastly, check for control validations and conditional
formatting.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app. We are
now getting a lot of US clients and they are finding problems with the date
format from our dd/mm/yy to their mm/dd/yy. So we are having to rework the
app to accomadate this dare I say strange logic and want to clarify that we
doing the right thing.

1. None of the current table fields that are date/time have any formatting
set at the table level so I presume we don't have to touch them (i.e. what
comes out of the query/form goes into the table)?
2. None of our code uses "#[value]#" so I presume we are ok there?
3. We use Date() and Now() extensively so I presume we don't have to touch
these as they will pick up the Windows system date format?
4. We have form text boxes that have a default value eg
=Format(Now(),"d/m/yyyy h:nn ampm") and a format of dd/mm/yyyy h:nn ampm.
These would have to be changed but how can we still keep the format other
then switching the d/m to m/d?
5. In our code when we have Format(Now, "dd/mm/yyyy h:mm ampm") these would
also have to be changed also?
6. When we have date calculations in our code as long as we don't stipulate
a hard format like dd/mm/yy and instead use a soft format like ShortDate or
MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume these are
also ok?

Basically it seems that we only need to change hard formats into soft
formats. For example where ever we have a hard format of "dd/mm/yyyy h:mm"
we need to change it to something like "General Date" - Is that right?

But also how do you do that without it displaying seconds?