473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Conversion From Text

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
4 2607
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
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
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
>
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Ray in HK | last post by:
Will it be possible to specify the date format of type DATE during data loading ?
1
by: michael montagne | last post by:
I have a delimited text file that has a column of dates in the form 2003-10-11 10:24:53. I can't get past a type conversion error when atempting to import this into a date/time column.
5
by: SalimShahzad | last post by:
Dear Respected Gurus, I need a coding assistant converting from gregorian to hijri and vice versa. I have problem with this codes. the problem Gregorian to Hijri gives 100% results. when i enter...
4
by: dhnriverside | last post by:
Hi peeps I have a datepicker control that's providing dates in the format dd/mm/yyyy (UK). I want to convert this to "yyyy-mm-dd" to store as a text field in my database (had lots of problems...
8
by: John Wildes | last post by:
Hello all I'm going to try and be brief with my question, please tell me if I have the wrong group. We are querying transaction data from a DB3 database application. The dates are stored as...
4
by: Phillip Vong | last post by:
VS2005 in VB.NET I have 2 simple textboxes (Textbox1, Textbox2) and they both have dates. Textbox1=12/31/2006 Textbox2=12/15/2006 I need to convert these two textboxes that are in String...
10
by: =?Utf-8?B?TWlrZQ==?= | last post by:
I have a string variable containing a date formatted as YYYYMMDD For example - Dim x as string = "20070314" If I try to perform a type conversion as follows I get an error: Dim y as Date =...
7
by: bruce.dodds | last post by:
Access seems to be handling a date string conversion inconsistently in an append query. The query converts a YYYYMM string into a date, using the following function: CDate(Right(,2) & "/1/" &...
4
by: Greg (codepug | last post by:
I have a text box with the date "08/14/2008" in it. I want to press a button and automatically calculate and place the date 1-year before in another text box. Result "08/12/2007". How do I...
2
by: RN1 | last post by:
A TextBox displays the current date (in dd/mm/yyyy format) & time when a user comes to a page (e.g. 15/10/2008 1:36:39 PM). To convert the date into international format so that the remote server...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.