Date conversions

  • Thread starter Thread starter .Len B
  • Start date Start date
L

.Len B

I have to import data from an ASCII text file and
use INSERT INTO ... to create records in an existing
table.

There are a couple of fields with dates in the form
Mar 05,10

I know that I can set the 9 characters into a string and
manipulate the string using format(... to massage the data
into a form suitable for the SQL string using #s.

I was just wondering if that technique is the most efficient
or is there a better approach?
 
I have to import data from an ASCII text file and
use INSERT INTO ... to create records in an existing
table.

There are a couple of fields with dates in the form
Mar 05,10

I know that I can set the 9 characters into a string and
manipulate the string using format(... to massage the data
into a form suitable for the SQL string using #s.

I was just wondering if that technique is the most efficient
or is there a better approach?
Click to expand...

Only a little massage is needed:

?cdate("Mar 05,10")
3/5/2010
 
Thanks John
That sure beats slicing and reassembling the string myself
which was what I had in mind.

I just knew there would be something better.

--
Len
______________________________________________________
remove nothing for valid email address.
| On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B"
<[email protected]>
| wrote:
|
| >I have to import data from an ASCII text file and
| >use INSERT INTO ... to create records in an existing
| >table.
| >
| >There are a couple of fields with dates in the form
| >Mar 05,10
| >
| >I know that I can set the 9 characters into a string and
| >manipulate the string using format(... to massage the data
| >into a form suitable for the SQL string using #s.
| >
| >I was just wondering if that technique is the most efficient
| >or is there a better approach?
|
| Only a little massage is needed:
|
| ?cdate("Mar 05,10")
| 3/5/2010
|
|
| --
|
| John W. Vinson [MVP]
|
 
On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B"

That actually is a valid date, so no conversion would be needed. Type
this in the Immediate window:
?IsDate("Mar 05,10")
True

-Tom.
Microsoft Access MVP
 
Thanks Tom,
John's suggestion didn't work as presented. I had to surround the
result with # characters to make it acceptable in the VALUES clause
of the INSERT INTO".

I expect yours will behave similarly. Both generate a True in IsDate.

I dare say that if I were to change my regional settings then I
wouldn't need any #.
--
Len
______________________________________________________
remove nothing for valid email address.
| On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B"
|
| That actually is a valid date, so no conversion would be needed. Type
| this in the Immediate window:
| ?IsDate("Mar 05,10")
| True
|
| -Tom.
| Microsoft Access MVP
|
|
| >I have to import data from an ASCII text file and
| >use INSERT INTO ... to create records in an existing
| >table.
| >
| >There are a couple of fields with dates in the form
| >Mar 05,10
| >
| >I know that I can set the 9 characters into a string and
| >manipulate the string using format(... to massage the data
| >into a form suitable for the SQL string using #s.
| >
| >I was just wondering if that technique is the most efficient
| >or is there a better approach?
 
I dare say that if I were to change my regional settings then I
wouldn't need any #.
Click to expand...

Nope. An INSERT INTO a date/time field needs # delimiters. The format is
irrelevant.
 
.Len B said:
I have to import data from an ASCII text file and
use INSERT INTO ... to create records in an existing
table.

There are a couple of fields with dates in the form
Mar 05,10

I know that I can set the 9 characters into a string and
manipulate the string using format(... to massage the data
into a form suitable for the SQL string using #s.

I was just wondering if that technique is the most efficient
or is there a better approach?
Click to expand...
 
Back
Top