Using Query

[Alan Tang]

Hello:

I have problem on processing the data. Would you mind to give me
some hints?

I have link a table with CSV file.
The first Column of CSV file is "08/01/2003 09:04:03.918"
How can I Split into two Column into the table?
E.G.
Column 1 Column 2
----------- ----------
08/01/2003 09:04:03

Thanks!

 

[jmonty]

Create a query, adding the linked table.
Add the field in question (we'll call Field1 for example
purposes) to the query by double-clicking it.
Use an alias to re-name the date and time portions of
Field1, and use the Left(), Mid() or Right() functions to
split out the data - See Access Help about how to use
these functions.
*** Alias Names: Do not use the Access reserved words Date
& Time use MyDate & MyTime or whatever you choose instead.

Field: MyDate: Left([Field1],10)|MyTime: Mid([Field1],12)
Table: |
Sort: |

In the example above, if Field1 = "08/01/2003 09:04:03.918"
MyDate will start from the left and contain the first 10
characters = "08/01/2003"
MyTime will start in character 12 and contain all the
characters from the 12th to the end = "09:04:03.918"

FYI: The Format() function will not work due to the fact
that the time portion of Field1 is not in the right format
(includes .918 and is missing AM or PM)

Hope I didn't confuse you.

 

[John Vinson]

Hello:

I have problem on processing the data. Would you mind to give me
some hints?

I have link a table with CSV file.
The first Column of CSV file is "08/01/2003 09:04:03.918"
How can I Split into two Column into the table?
E.G.
Column 1 Column 2
----------- ----------
08/01/2003 09:04:03

Thanks!

You can use the InStr, Left and Mid functions to pick apart the
string:

DatePart: Left([Column1], InStr([Column1], " ") - 1)
TimePart: Mid([Column1], InStr([Column1], " ") + 1)

But you might do better to store this entire date/time value in a
single date/time field; this datatype stores a Double Float count of
days (the integer portion) and times (the fractional part). Try

CDate(Left([Column1], InStr([column1], ".") - 1)