i need to convert text to date

  • Thread starter Thread starter Jordan Shoderu
  • Start date Start date
J

Jordan Shoderu

can anyone help me i have a field in my database with dates but they are
imported as text (20040129) as you can see i need the whole coloum to be
converted in the normal type date stuff DD-MMM-YYYY (ie 07-Aug-2004)

can this be don and how to i do it

thank you

Jordan
 
Hi,

try the Left(Field,Length), Right(Field,Length), Mid(Field,Start,Length)

e.g. DateStuff : Right(ImportedDateField, 2) & "/" & Mid(ImportedDateField,
5, 2) & "/" & Left (ImportedDateField, 4)
This should give you in your example : 29/01/2004, which Access understands
as a date.

HTH
Bernd
 
Yes, Access will recognize 29/01/2004 as a date, but what is it going to do
with 12/01/2004? Based on how you wrote January 1st, I'm assuming you'd want
that interpretted as 12 Jan, 2004. However, Access is going to interpret
that as 01 Dec, 2004, regardless of what your regional settings are, in many
cases. It handles the first correctly because there is no 29th month, but
any time the first part of the date is 12 or less, it's going to assume
mm/dd/yyyy format.

You're much better off using DateSerial, as I posted elsewhere in this
thread.
 
Got me beat why anyone would ever want the month before the day followed by
the year. Illogical. But then again I suppose miles, inches, pounds, tons,
driving on the right side of the road and gallons are also illogical.
:P
 
No argument from me... yyyy-mm-dd is the only one that makes sense, as far
as I'm concerned.
 
I think he's an Aussie. Speed limits are what keep you from getting to the
footie game on time, and miles are how far you are willing to walk for
another Victoria Bitter!
 
Doug said:
I think he's an Aussie. Speed limits are what keep you from getting
to the footie game on time, and miles are how far you are willing to
walk for another Victoria Bitter!
Click to expand...

Yep, you got it Doug although I prefer Carlton Draught to VB :) It's < 1km
to my local pub.

(And Bernd: McBain is a Scottish name).

Everything here is hard metric and since I was born in 69, I can't believe
that imperial or US measurement systems are still in use anywhere. If they
were hex or octal, maybe....
 
I'M FROM TURKEY
Click to expand...

Welcome to the microsoft.public.access newsgroup, dedicated to Questions and
Answers about Microsoft Access database software. We look forward to seeing
your Access questions and comments.

Larry Linson
Microsoft Access MVP
 
Back
Top