changing date format

[Jean-Paul]

Hi,
I have a table where dates are stored in foramt: dd/mm/yyyy
Now I have difficulties running SQL or filters.
I was told I should use the mm/dd/yyyy format.
How can I change the format of exsisting records
Thanks
JP

 

[Rick Brandt]

Hi,
I have a table where dates are stored in foramt: dd/mm/yyyy
Now I have difficulties running SQL or filters.
I was told I should use the mm/dd/yyyy format.
How can I change the format of exsisting records
Thanks
JP

Your existing records are not stored with a format. DateTimes are stored as
numbers. Right now as I post the DateTime (according to Access) is...

39592.5571296296

 

[Evi]

You don't need to do this, Jean-Paul. You can change your format when you
actually run a sql statement or a query.

Format([YourDateField]),"0") works very well in code (for instance, if you
are filtering via a text box in a form) so that you can treat the field as
if it was a number.
In Sql statements themselves, turn the date around so that you type
#05/17/2008#

If filtering directly in queries, Access seems quite happy with what you
have in your Regional Settings ie here in the Uk, I type the 5th of March as
#05/03/2008# in the Criteria Row of my query and it filters just fine while
a peek at the Sql statement shows that it has been obligingly converted to
#03/05/2008#

If your region is not accustomed to the mm/dd/yyyy format, then it will be
easy for your inputters to make mistakes when entering dates into the tables
or cause confusion when they are reading data.

Evi

 

[Jean-Paul]

Thanks Evi for your kind reply...
I finally arrived solving the date problem in the SQL statement...
PFFFFFFFFFF how confusing
JP, Belgium (storing a date as dd/mm/yyyy)

You don't need to do this, Jean-Paul. You can change your format when you
actually run a sql statement or a query.

Format([YourDateField]),"0") works very well in code (for instance, if you
are filtering via a text box in a form) so that you can treat the field as
if it was a number.
In Sql statements themselves, turn the date around so that you type
#05/17/2008#

If filtering directly in queries, Access seems quite happy with what you
have in your Regional Settings ie here in the Uk, I type the 5th of March as
#05/03/2008# in the Criteria Row of my query and it filters just fine while
a peek at the Sql statement shows that it has been obligingly converted to
#03/05/2008#

If your region is not accustomed to the mm/dd/yyyy format, then it will be
easy for your inputters to make mistakes when entering dates into the tables
or cause confusion when they are reading data.

Evi

Hi,
I have a table where dates are stored in foramt: dd/mm/yyyy
Now I have difficulties running SQL or filters.
I was told I should use the mm/dd/yyyy format.
How can I change the format of exsisting records
Thanks
JP

 

[Tony Toews [MVP]]

I have a table where dates are stored in foramt: dd/mm/yyyy

Actually the dates are displayed in dd/mm/yyyy format according to
your systems Regional Settings.

Now I have difficulties running SQL or filters.
I was told I should use the mm/dd/yyyy format.
How can I change the format of exsisting records

SQL statements require that the dates be either completely unambiguous
or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do
it's best to interpret the date with unknown results depending on the
specific date it is working with. You can't assume that the system
you are working on is using those date formats. Thus you should use
the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/