By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Date Conversion From Text

P: n/a
What is the easiest way to force Access to read a date in a specific
format?

I have an application that imports a delimited text file with a date
field in the format "mm/dd/yyyy".

I've written a function that converts it to a numeric date (integer)..
and it's quite a long, convoluted function (it converts it to a medium
date in the process). This may seem completely insane, but it was the
only way I could think of to ensure that Access reads it properly.
(Access can never seem to make up it's mind whether it wants to read a
date as mm/dd/yy or dd/mm/yy - I don't trust CVDate).

Any thoughts on a more efficient function that would perform the same
task?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
CDate() interprets the string according to the user's settings in the
Windows Control Panel under Regional Options. That should be satisfactory
unless the text file might be generated in one country and interpreted in
another.

To write your own, use:
DateSerial(Right([td], 4), Left([td],2), Mid([td],4,2))
where "td" represents the text date field name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sandy Fleming" <ms*****@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
What is the easiest way to force Access to read a date in a specific
format?

I have an application that imports a delimited text file with a date
field in the format "mm/dd/yyyy".

I've written a function that converts it to a numeric date (integer)..
and it's quite a long, convoluted function (it converts it to a medium
date in the process). This may seem completely insane, but it was the
only way I could think of to ensure that Access reads it properly.
(Access can never seem to make up it's mind whether it wants to read a
date as mm/dd/yy or dd/mm/yy - I don't trust CVDate).

Any thoughts on a more efficient function that would perform the same
task?

Nov 12 '05 #2

P: n/a
On 11 Feb 2004 17:20:42 -0800, Sandy Fleming wrote:
What is the easiest way to force Access to read a date in a specific
format?

I have an application that imports a delimited text file with a date
field in the format "mm/dd/yyyy".

I've written a function that converts it to a numeric date (integer)..
and it's quite a long, convoluted function (it converts it to a medium
date in the process). This may seem completely insane, but it was the
only way I could think of to ensure that Access reads it properly.
(Access can never seem to make up it's mind whether it wants to read a
date as mm/dd/yy or dd/mm/yy - I don't trust CVDate).

Any thoughts on a more efficient function that would perform the same
task?


Dates are read the same way regardless of format you apply to them. They're
just numbers. When you specify one of the built in formats (ie. "Short
Date", "Long Date") it's using the regional settings of the computer it's
being viewed on. The simplest way to enforce uniformity is to specify the
same format on all date entry points without using the built in formats.
There is no need to convert a date when changing it's format.
--
Mike Storr
veraccess.com
Nov 12 '05 #3

P: n/a
CDB
Create an import specification, including the date-recognition format, and
specify that format in the import call.

Clive
"Sandy Fleming" <ms*****@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
What is the easiest way to force Access to read a date in a specific
format?

I have an application that imports a delimited text file with a date
field in the format "mm/dd/yyyy".

I've written a function that converts it to a numeric date (integer)..
and it's quite a long, convoluted function (it converts it to a medium
date in the process). This may seem completely insane, but it was the
only way I could think of to ensure that Access reads it properly.
(Access can never seem to make up it's mind whether it wants to read a
date as mm/dd/yy or dd/mm/yy - I don't trust CVDate).

Any thoughts on a more efficient function that would perform the same
task?

Nov 12 '05 #4

P: n/a
>
To write your own, use:
DateSerial(Right([td], 4), Left([td],2), Mid([td],4,2))
where "td" represents the text date field name.


That's what I was looking for. Didn't know about DateSerial. This is
what I need because my application runs on one of four different
Citrix servers, and I'm not sure that they have consistent regional
settings (IT has changed them on me before).

Thanks.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.