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?
4  2643 
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(Righ t([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*****@hotmai l.com> wrote in message
news:4b******** *************** ***@posting.goo gle.com... 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?
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
Create an import specification, including the date-recognition format, and
specify that format in the import call.
Clive
"Sandy Fleming" <ms*****@hotmai l.com> wrote in message
news:4b******** *************** ***@posting.goo gle.com... 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?
> To write your own, use:
DateSerial(Righ t([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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ray in HK |
last post by:
Will it be possible to specify the date format of type DATE during data
loading ?
|
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.
|
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 this to Hijri to gregorian it either
do nothing or go to next record on form. well there r two fields on
form that r calc afterevents each other dates.
ur assistance will be highly appreciated, if any one can tell what is
wrong with this codes
|
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 with date conversions using "proper" data
fields).
I used to use ASP, and it was easy, using a combination of Left() Mid() and
|
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 text fields. Each date for example 10/31/03 or October 31st
2003 is stored as 10/31/A3 in the system. My reasoning for this is because
they couldn't solve their Y2K problem or this is their solution to it. All
dates prior to 2000 are stored...
| | |
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 format into Date
format and I'm not sure how. I tried this and I thought it would work, but
I get the
|
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 = CDATE(x)
How can I perform a type conversion for the date format YYYYMMDD?
Thanks.
|
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/" &
Left(,4))
I entered the string "200715" in a record to test an error condition.
|
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 convert (Date) "08/14/2008" to "08/14/2007" as a legitimate
date, not a text string???
I tried this but I'm getting a string, not a proper date value:
txtStartDate = Month(Date) & " " & Day(Date) & " " & Year(Date) + 1
|
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 doesn't
generate any error, this is what I am doing:
---------------------------
Dim strSDate As String
Dim strSTime As String
Dim strStartDT As String
Dim strSDateTime As String
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
