text format to date format

[Achit via AccessMonster.com]

I have 2 question:
1. I export data from excel 4.0 to Access 2003, in Excel my data have text
format, I want change data to date format...
example my data in excel like this 2009-11-23-08.25.24
I want change to date format 23-11-2009 08:25:24

2. I have data like this CPN09003947-1-1-CPN31000006 and I want take that
data until after "-", but some time count of data is different example
HSJA22160061-1-1-SJA20900332


please help me,,,
what can I do????

 

[John Spencer]

1) You can use an expression like the following to create the needed dateTime.
If you want to permanently change the data you can use the expression in an
update query and update a new field to the calculated value.

CDate(Left([TextField],10)) + CDate(Replace(Right([TextField]),8,".",":"))

If you have blanks/Nulls for the TextField this will error so you might want
to check that there is data there in the first place.

2) It is not clear what portion of the data you want returned.
Given CPN09003947-1-1-CPN31000006 do you want
CPN09003947
CPN09003947-1 or
CPN09003947-1-1
or do you want some other portion of that string returned.

The first is the simplest.
LEFT([TextField],Instr(1,[Textfield],"-")-1)

The second can be accomplished with
LEFT([TextField],Instr(Instr(1,[Textfield],"-")+1,[Textfield],"-")-1)

The third (with later versions of Access) with
LEFT([TextField],InstrRev([Textfield],"-")-1)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Achit via AccessMonster.com]

1) You can use an expression like the following to create the needed dateTime.
If you want to permanently change the data you can use the expression in an
update query and update a new field to the calculated value.

CDate(Left([TextField],10)) + CDate(Replace(Right([TextField]),8,".",":"))

If you have blanks/Nulls for the TextField this will error so you might want
to check that there is data there in the first place.

2) It is not clear what portion of the data you want returned.
Given CPN09003947-1-1-CPN31000006 do you want
CPN09003947
CPN09003947-1 or
CPN09003947-1-1

Thanks...
or do you want some other portion of that string returned.

The first is the simplest.
LEFT([TextField],Instr(1,[Textfield],"-")-1)

The second can be accomplished with
LEFT([TextField],Instr(Instr(1,[Textfield],"-")+1,[Textfield],"-")-1)

The third (with later versions of Access) with
LEFT([TextField],InstrRev([Textfield],"-")-1)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have 2 question:
1. I export data from excel 4.0 to Access 2003, in Excel my data have text

[quoted text clipped - 8 lines]

please help me,,,
what can I do????