International Date Format Clarification

  • Thread starter Thread starter Ian Baker
  • Start date Start date
I

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?
 
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
 
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
-
Ian Baker said:
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
-
Graham R Seach said:
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 said:
Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app.
Click to expand...
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
Click to expand...
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
Click to expand...
ShortDate
or
MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
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

Ian Baker said:
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
-
Graham R Seach said:
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 said:
Hi
We are using Access 2000, 2002 & 2003. We have an application that was
developed in Australia and uses dates extensively throughout the app.
Click to expand...
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
Click to expand...
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
Click to expand...
ShortDate
or
MediumTime we are also ok?
7. We also use functions like Month() or Year() so again I presume
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
-
Graham R Seach said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top