Changing format from record to record in the same field

[Guest]

Hi all,
I have a big a problem with my "receiving date" field,it's formatted as
mm/dd/yyy ,this was working very well for 4 months (specifically from
23/11/2006 till 28/02/2007),what happened is that starting from March SOME
records on the same field are formatted mm/dd/yyy as normal formatting but
others are formatted as dd/mm/yyyy which causes a big problem in my database
that depends basically on the "receiving date" field !!
My database is linked to the inbox of Outlook,so the emails go
automatically into the database,one the fields of the database is formatted
as "Long date" so to combined the field "receiving date" i write the
following code in a query :
(DatePart("m",[Received])) & "/" & (DatePart("d",[Received])) & "/" &
(DatePart("yyyy",[Received]))
Can anybody help me keep the format mm/dd/yyyy to all the records of the
field "receiving date" ?

 

[Douglas J. Steele]

The problem isn't the formatting: the problem is that the wrong values have
been stored in the field.

Date fields in Access are actually 8 byte floating point numbers, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899 (and the decimal portion represents the time as a fraction of a
day). That means that dates are not stored with any particular format: the
format is simply applied at display time.

You may find it useful to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or my September, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

Thank you very much for your answer ,actually i tried to read what you
described below,but i could not get a quick answer to my question,i want to
know what should i do excactly to solve the problem,otherwisw my work will be
in danger..

The problem isn't the formatting: the problem is that the wrong values have
been stored in the field.

Date fields in Access are actually 8 byte floating point numbers, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899 (and the decimal portion represents the time as a fraction of a
day). That means that dates are not stored with any particular format: the
format is simply applied at display time.

You may find it useful to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or my September, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
I have a big a problem with my "receiving date" field,it's formatted as
mm/dd/yyy ,this was working very well for 4 months (specifically from
23/11/2006 till 28/02/2007),what happened is that starting from March SOME
records on the same field are formatted mm/dd/yyy as normal formatting but
others are formatted as dd/mm/yyyy which causes a big problem in my
database
that depends basically on the "receiving date" field !!
My database is linked to the inbox of Outlook,so the emails go
automatically into the database,one the fields of the database is
formatted
as "Long date" so to combined the field "receiving date" i write the
following code in a query :
(DatePart("m",[Received])) & "/" & (DatePart("d",[Received])) & "/" &
(DatePart("yyyy",[Received]))
Can anybody help me keep the format mm/dd/yyyy to all the records of the
field "receiving date" ?

 

[John W. Vinson]

Thank you very much for your answer ,actually i tried to read what you
described below,but i could not get a quick answer to my question,i want to
know what should i do excactly to solve the problem,otherwisw my work will be
in danger..

What you need to do is store the correct data in the first place.

The problem is NOT the format of the dates in Access.

If some of your [Received] dates are in mm/dd/yyyy format, and others
are in dd/mm/yyyy format, you CANNOT TELL whether 3/5/2007 refers to
March 5 or May 3.

If you put incorrect dates into the table, you have incorrect dates in
the table - and no amount of formatting will change that fact!


John W. Vinson [MVP]

 

[Douglas J. Steele]

Sorry, I can't explain it any more concisely than I did in the article.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you very much for your answer ,actually i tried to read what you
described below,but i could not get a quick answer to my question,i want
to
know what should i do excactly to solve the problem,otherwisw my work will
be
in danger..

The problem isn't the formatting: the problem is that the wrong values
have
been stored in the field.

Date fields in Access are actually 8 byte floating point numbers, where
the
integer portion represents the date as the number of days relative to 30
Dec, 1899 (and the decimal portion represents the time as a fraction of a
day). That means that dates are not stored with any particular format:
the
format is simply applied at display time.

You may find it useful to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or my September, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can
download
the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,
I have a big a problem with my "receiving date" field,it's formatted
as
mm/dd/yyy ,this was working very well for 4 months (specifically from
23/11/2006 till 28/02/2007),what happened is that starting from March
SOME
records on the same field are formatted mm/dd/yyy as normal formatting
but
others are formatted as dd/mm/yyyy which causes a big problem in my
database
that depends basically on the "receiving date" field !!
My database is linked to the inbox of Outlook,so the emails go
automatically into the database,one the fields of the database is
formatted
as "Long date" so to combined the field "receiving date" i write the
following code in a query :
(DatePart("m",[Received])) & "/" & (DatePart("d",[Received])) & "/" &
(DatePart("yyyy",[Received]))
Can anybody help me keep the format mm/dd/yyyy to all the records of
the
field "receiving date" ?