Storing a date in UK format

[Duncs]

I have a table that records entry & exit date / time into a database.
In an SQL query within the form's Open evend, I have the following:

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, UserName,
WhereLoaded) " & _
"VALUES(#" & Format(Now(), "dd/mm/yyyy hh:mm:ss") &
"#, '" & _
Environ("computername") & "', '" & Environ("username")
& "', '" & _
CodeProject.FullName & "');"
DoCmd.RunSQL strSQL

This works fine, apart from the dates. These are stored in US format
rather than UK. This results in some confusion. For example, if I
load the database on 9th June 2010, my date in will be recorded as
06/09/2010. However, if I load it on 21st June, the Date will be
recorded as 21/06/2010. The DateIn field is defined as a GeneralDate.

Any ideas?

Duncs

 

[Rick Brandt]

I have a table that records entry & exit date / time into a database.
In an SQL query within the form's Open evend, I have the following:

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, UserName,
WhereLoaded) " & _
"VALUES(#" & Format(Now(), "dd/mm/yyyy hh:mm:ss") &
"#, '" & _
Environ("computername") & "', '" & Environ("username")
& "', '" & _
CodeProject.FullName & "');"
DoCmd.RunSQL strSQL

This works fine, apart from the dates. These are stored in US format
rather than UK. This results in some confusion. For example, if I
load the database on 9th June 2010, my date in will be recorded as
06/09/2010. However, if I load it on 21st June, the Date will be
recorded as 21/06/2010. The DateIn field is defined as a GeneralDate.

Any ideas?

Duncs

Formatting has nothing to do with storage. Dates are stored internally as
numbers. It is only a *display* attribute. You need to set formatting every
place users see them.

Also, in certain situations when querying or filter on dates you MUST use
US, ISO, or a non-ambiguous format (month using alpha for example).

 

[Duncs]

Formatting has nothing to do with storage. Dates are stored internally as
numbers. It is only a *display* attribute.  You need to set formatting every
place users see them.

Also, in certain situations when querying or filter on dates you MUST use
US, ISO, or a non-ambiguous format (month using alpha for example).- Hidequoted text -

- Show quoted text -

The only problem is, when I view the date / time in the table it is
showing in both dd/mm/yyyy & mm/dd/yyyy format, depending on the date
of access. What I want to know is, is there anyway I can force the
display in the table to be dd/mm/yyyy?

Duncs

 

[John Spencer]

You need to used a format that forces NOW into the US format or into the ISO
format in your INSERT query.

"VALUES(" & Format(Now(), "\#yyyy-mm-dd hh:mm:ss\#") & ...

The date is actually stored as number (type double) which represents the
number of days and amount of time the date and time is from the Zero date
(midnight 12/30/1899).

For a more detailed explanation of the date problem take a look at
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Rick Brandt]

The only problem is, when I view the date / time in the table it is
showing in both dd/mm/yyyy & mm/dd/yyyy format, depending on the date
of access. What I want to know is, is there anyway I can force the
display in the table to be dd/mm/yyyy?

Tables are for storage, not display. That being said you should be able to
set the format property of that field in design view of the table.

Note that this will not propagate. If you use the table data in a query,
form, or report, you will have to set the format in each one of them.

 

[Duncs]

Tables are for storage, not display.  That being said you should be able to
set the format property of that field in design view of the table.

Note that this will not propagate.  If you use the table data in a query,
form, or report, you will have to set the format in each one of them.

I don't want to display the table directly.

There are two scenarios where I am having problems:

1. In the table scenario mentioned, the date in is being recorded /
shown incorrectly. As the admin of the database, I need to check on
this log table and see who hasn't logged out correctly, who has done
what etc. Since the date is showing as 09/06/2010 for the 6th
September, but 15/09/2010 for the 15th September this causes confusion
when I scan down the table to check on dates. The format of the date
field is General, so I would have thought it would show it as dd/mm/
yyyy.

2. Part of the process is loading several data files into tables.
Part of this process records the date on which the file was uploaded
into a Date/Time field in the table. Again, for files loaded on the
6th September 2010, the DateLoaded field shows as 09/06/2010 whereas
files loaded on 15th September show the DateLoaded as 15/09/2010.
Once again, the DateLoaded field in teh table is defined as a
GeneralDate field.

Duncs

 

[Douglas J. Steele]

If your regional settings are set to display days in dd/mm/yyyy format, then
09/06/2010 is NOT the 6th of
September: you actually stored the 9th of June in the table.

Take a look at what Allen Browne has at
http://www.allenbrowne.com/ser-36.html

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)

Tables are for storage, not display. That being said you should be able to
set the format property of that field in design view of the table.

Note that this will not propagate. If you use the table data in a query,
form, or report, you will have to set the format in each one of them.

I don't want to display the table directly.

There are two scenarios where I am having problems:

1. In the table scenario mentioned, the date in is being recorded /
shown incorrectly. As the admin of the database, I need to check on
this log table and see who hasn't logged out correctly, who has done
what etc. Since the date is showing as 09/06/2010 for the 6th
September, but 15/09/2010 for the 15th September this causes confusion
when I scan down the table to check on dates. The format of the date
field is General, so I would have thought it would show it as dd/mm/
yyyy.

2. Part of the process is loading several data files into tables.
Part of this process records the date on which the file was uploaded
into a Date/Time field in the table. Again, for files loaded on the
6th September 2010, the DateLoaded field shows as 09/06/2010 whereas
files loaded on 15th September show the DateLoaded as 15/09/2010.
Once again, the DateLoaded field in teh table is defined as a
GeneralDate field.

Duncs

 

[David W. Fenton]

when querying or filter on dates you MUST use
US, ISO, or a non-ambiguous format

Minor quibble:

you MUST use US, ISO or some other non-ambiguous format

That is, ISO format works because it's non-ambiguos. Otherwise, it
is wholly unknown to Access (it is not a named format -- that really
ought to change in an upcoming version of Access, since it's been
the accepted standard for a very long time now).

 

[Rick Brandt]

I don't want to display the table directly.

There are two scenarios where I am having problems:

1. In the table scenario mentioned, the date in is being recorded /
shown incorrectly. As the admin of the database, I need to check on
this log table and see who hasn't logged out correctly, who has done
what etc. Since the date is showing as 09/06/2010 for the 6th
September, but 15/09/2010 for the 15th September this causes confusion
when I scan down the table to check on dates. The format of the date
field is General, so I would have thought it would show it as dd/mm/
yyyy.

Something is incorrect about what you are saying. A table might show
different formats when viewed on different computers, but it will not show
different formats on different rows when viewed on a single PC.

 

[John W. Vinson]

Something is incorrect about what you are saying. A table might show
different formats when viewed on different computers, but it will not show
different formats on different rows when viewed on a single PC.

I'm guessing that incorrect data has been entered as a result of the confusion
between different regional conventions. A user might have entered 6/2/2010
with the *intent* of entering the sixth of February and not realized that
Access was storing June the second.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Duncs]

I'm guessing that incorrect data has been entered as a result of the confusion
between different regional conventions. A user might have entered 6/2/2010
with the *intent* of entering the sixth of February and not realized that
Access was storing June the second.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

I'm getting really confused here guys.

When I execute the SQL code to insert the dat, it is inserted as:

#" & Format(Now(), "dd-mmm-yyyy hh:mm:ss") & "#

Follishly, it would appear, I belived that this would store the 9th
June 2010 as 09-Jun-2010. But, when I view the table, it shows the
date as 06-Sep-2010. This code is used in a routine to log who has
logged in and when. When I want to quickly look and see who is in it,
the dates show incorrectly.

Also, there is a routine that reads infomoration from an external
file. The file has the date at the start of the filename, in the
format DD-MM-YY. So, when the import is carried out, I extract this
date and insert it iinto the table as the date for the file. Again,
this date is inserted into the table as:

#" & Format(FileDate, "dd-mmm-yyyy hh:mm:ss") & "#

But, yet again, it stores the 9th June 2010 as 06-Sep-2010. This has
the effect of cocking up queries which are based upon file dates.

All I want to do is read / store the date in the format DD-MMM-YYYY.

Duncs

 

[John W. Vinson]

When I execute the SQL code to insert the dat, it is inserted as:

#" & Format(Now(), "dd-mmm-yyyy hh:mm:ss") & "#

Follishly, it would appear, I belived that this would store the 9th
June 2010 as 09-Jun-2010. But, when I view the table, it shows the
date as 06-Sep-2010. This code is used in a routine to log who has
logged in and when. When I want to quickly look and see who is in it,
the dates show incorrectly.

Also, there is a routine that reads infomoration from an external
file. The file has the date at the start of the filename, in the
format DD-MM-YY. So, when the import is carried out, I extract this
date and insert it iinto the table as the date for the file. Again,
this date is inserted into the table as:

#" & Format(FileDate, "dd-mmm-yyyy hh:mm:ss") & "#

But, yet again, it stores the 9th June 2010 as 06-Sep-2010. This has
the effect of cocking up queries which are based upon file dates.

All I want to do is read / store the date in the format DD-MMM-YYYY.

Duncs

You and a lot of other folks! It *is* confusing.

Data *STORAGE* is not the same as data *DISPLAY*. A date/time field is
actually stored as a number. What's stored in the table is neither "9th June
2010" nor "6/9/2010" nor "9/6/2010" - what's stored in the table is
40427.000000000 - 40427 days since midnight, December 30, 1899.

When you insert a literal date string you must - MUST!!!! - use either a
m/d/y format, or an unambiguous format. The programmers who wrote Access were
Americans, and used the American month/day/year convention; if you insert a
date literal "09-06-2010" Access will totally ignore your regional setting,
and use the American convention and store it as September 6.

You can set the Format property of the field in the table to "dd-mm-yyyy" if
you want it to display 09-06-2010, but - again - that's just a display setting
and does not affect what's stored. If you just want to store the date rather
than the date and time accurate to milliseconds, I'd use

#" & Format(Date(), "yyyy-mm-dd") & "#

using Date() - just the date, with a midnight (.0) time portion, formatted in
the ISO unambiguous form.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Duncs]

You and a lot of other folks! It *is* confusing.

Data *STORAGE* is not the same as data *DISPLAY*. A date/time field is
actually stored as a number. What's stored in the table is neither "9th June
2010" nor "6/9/2010" nor "9/6/2010" - what's stored in the table is
40427.000000000 - 40427 days since midnight, December 30, 1899.

When you insert a literal date string you must -  MUST!!!!  - use either a
m/d/y format, or an unambiguous format. The programmers who wrote Access were
Americans, and used the American month/day/year convention; if you inserta
date literal "09-06-2010" Access will totally ignore your regional setting,
and use the American convention and store it as September 6.

You can set the Format property of the field in the table to "dd-mm-yyyy"if
you want it to display 09-06-2010, but - again - that's just a display setting
and does not affect what's stored. If you just want to store the date rather
than the date and time accurate to milliseconds, I'd use

#" & Format(Date(), "yyyy-mm-dd") & "#

using Date() - just the date, with a midnight (.0) time portion, formatted in
the ISO unambiguous form.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Sorry, I've just noticed the problem...

I've titled the post 'Storing a date...' when it should have been
'Showing a date...'. Looks like I've been the author of my own
misfortune!!

Now John, yours and everyone elses posts make sense!! I'll try what
you suggest and let you know how I get on.

Duncs

 

[David W. Fenton]

:

Now John, yours and everyone elses posts make sense!! I'll try
what you suggest and let you know how I get on.

The issue John is explaining is that the dates are stored as a
double, i.e., a numeric value, whereas for human-friendly reasons,
we display and type our dates in string representations. When you
use the # delimiters, you're telling Access "Hey, Access! I'm
passing you a string that represents a date -- convert it for me,
please!"

Since there are a number of variations in the string
representations, you have to use an unambiguous one to make sure
that Access/VBA/Jet/ACE converts to the intended numeric value for
storage. If you don't use an unambiguous string representation,
Access assumes you're passing a US format date. That assumption may
be wrong, so you have to give Access a string representation that it
can't interpret differently than you intend.

 

[Duncs]

I have a table that records entry & exit date / time into a database.
In an SQL query within the form's Open evend, I have the following:

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, UserName,
WhereLoaded) " & _
                "VALUES(#" & Format(Now(), "dd/mm/yyyy hh:mm:ss") &
"#, '" & _
                Environ("computername") & "', '" & Environ("username")
& "', '" & _
                CodeProject.FullName & "');"
DoCmd.RunSQL strSQL

This works fine, apart from the dates.  These are stored in US format
rather than UK.  This results in some confusion.  For example, if I
load the database on 9th June 2010, my date in will be recorded as
06/09/2010.  However, if I load it on 21st June, the Date will be
recorded as 21/06/2010.  The DateIn field is defined as a GeneralDate.

Any ideas?

Duncs

I don't know if this is a good idea, but I wanted to clarify the
situation I am in and what I'm looking for help on.

I have a table, into which is read registration data, from an external
file, on a daily basis. So, the file I receive today is for all
registrations made yesterday. For MI purposes, I need to record the
date that the registrations were made, which is not in the file. The
process I follow is, in a VBA module:

1. Rename the file, appending yesterdays date at the start, in the
fromat dd-mm-yy
2. Read the file into the registrations table in access
3. Run a SQL update query, using DoCmd.RunSQL to update the
DateRegistered field in the table, to the value that has been appended
to the start of the file
4. Now, for any MI queries, I should be able to tell how many
customers registered on specific days etc.

This is where problems start to crop up. After I've ran the import
and updated the date field, if I open the table and view the data, it
appears in the format mm/dd/yyyy, but not for all dates. So,, for
example, for all customers registered on 12th September 2010, the
registration date appears in the table as 09/12/2010. But, for all
customers registered the following day, 13th September 2010, the
registration date appears in the table as 13/09/2010.

This means that when I'm trying to retrieve MI for registrations,
accounts registered on the 6th September are counted as though they
were registered on the 9th June, which really screws my data.

So, what I need to know is how can I get consistency when storing /
retrieving / manipulating dates?

TIA

Duncs

 

[Douglas J. Steele]

Sounds like you didn't pay attention to the earlier discussion!

Read what Allen Browne has at http://www.allenbrowne.com/ser-36.html

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele/AccessIndex.html
Co-author: "Access 2010 Solutions", published by Wiley
(no private e-mails, please)

I have a table that records entry & exit date / time into a database.
In an SQL query within the form's Open evend, I have the following:

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, UserName,
WhereLoaded) " & _
"VALUES(#" & Format(Now(), "dd/mm/yyyy hh:mm:ss") &
"#, '" & _
Environ("computername") & "', '" & Environ("username")
& "', '" & _
CodeProject.FullName & "');"
DoCmd.RunSQL strSQL

This works fine, apart from the dates. These are stored in US format
rather than UK. This results in some confusion. For example, if I
load the database on 9th June 2010, my date in will be recorded as
06/09/2010. However, if I load it on 21st June, the Date will be
recorded as 21/06/2010. The DateIn field is defined as a GeneralDate.

Any ideas?

Duncs

I don't know if this is a good idea, but I wanted to clarify the
situation I am in and what I'm looking for help on.

I have a table, into which is read registration data, from an external
file, on a daily basis. So, the file I receive today is for all
registrations made yesterday. For MI purposes, I need to record the
date that the registrations were made, which is not in the file. The
process I follow is, in a VBA module:

1. Rename the file, appending yesterdays date at the start, in the
fromat dd-mm-yy
2. Read the file into the registrations table in access
3. Run a SQL update query, using DoCmd.RunSQL to update the
DateRegistered field in the table, to the value that has been appended
to the start of the file
4. Now, for any MI queries, I should be able to tell how many
customers registered on specific days etc.

This is where problems start to crop up. After I've ran the import
and updated the date field, if I open the table and view the data, it
appears in the format mm/dd/yyyy, but not for all dates. So,, for
example, for all customers registered on 12th September 2010, the
registration date appears in the table as 09/12/2010. But, for all
customers registered the following day, 13th September 2010, the
registration date appears in the table as 13/09/2010.

This means that when I'm trying to retrieve MI for registrations,
accounts registered on the 6th September are counted as though they
were registered on the 9th June, which really screws my data.

So, what I need to know is how can I get consistency when storing /
retrieving / manipulating dates?

TIA

Duncs

 

[Duncs]

Doug,

I'm getting confused, or just stressed out with things.

I've read what Allen says on the site, but I'm still none the wiser.
I do indeed use an SQL statement to update the table. The SQL string
looks like this:

strSql = "UPDATE tblRegistered SET
tblRegistered.DateRegistered = '" & fileDate & _
"' WHERE (((tblRegistered.DateRegistered) Is
Null));"

Where file date is setup as the first 8 characters of the filename,
which is in the format dd-mm-yy. When I inspect this on runtime, the
SQL statement shows, for example:

"UPDATE tblRegistered SET tblRegistered.DateRegistered =
#06/09/2010# WHERE (((tblRegistered.DateRegistered) Is Null));

However, once this value is updated in the table, the table shows it
as 09/06/2010.

I'm just not getting my head round these dates! I understand that
they are stored as numbers, and not dates. However, all I want is for
the table to show the date as dd/mm/yyyy.

I'm sorry if this is sounding stupid, but I'm just not grasping why
the date isn't shown how I want.

Duncs

 

[John W. Vinson]

I'm just not getting my head round these dates! I understand that
they are stored as numbers, and not dates. However, all I want is for
the table to show the date as dd/mm/yyyy.

You're confusing data STORAGE with data DISPLAY.

A date is a specific point in time. It's not a text string. The first two
characters of a date are *NOT* a day, nor are they a month!

What you need to do is store the specific point in time in your table, and
then use a Format - dd/mm/yyyy in your case - when you want to *display* that
date. It shouldn't matter to you how the date appears when you open the table
datasheet (though you can set the Format property of the field however you
like), since you shouldn't be using table datasheets to interact with the data
in any case.

You say:

strSql = "UPDATE tblRegistered SET
tblRegistered.DateRegistered = '" & fileDate & _
"' WHERE (((tblRegistered.DateRegistered) Is
Null));"

Where file date is setup as the first 8 characters of the filename,
which is in the format dd-mm-yy. When I inspect this on runtime, the
SQL statement shows, for example:

Bear in mind that a literal date in a Query will be interpreted in American
mm/dd/yyyy format if that's possible. For instance, 18/09/2010 will be
interpreted as 18th September (because there is no 18th month), but 09/06/2010
will be read as September 6 - American month/day/year interpretation - because
the programmers were Americans and chose that convention!

Try changing your strSQL to

strSql = "UPDATE tblRegistered SET
tblRegistered.DateRegistered = #" & Format(CDate(fileDate, "mm/dd/yyyy") & _
"# WHERE (((tblRegistered.DateRegistered) Is
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com