Date Timestamp Format

[KarenY]

I have the same question as one of authors submitted about the problem in
converting the Timestamp format into a normal date format and I found answer
as listed below. However I couldn't get it worked ?? Please help.
My field is a text field from an imported file, and I am using Access 2003,
Select Query to run the date format on a new field from the table.

Answer:

If it is a text field then you need --
Val(Right(Left([YourField],7),2)&"/"&
Val(Right(Left([YourField],10),2)&"/"& Val(Left([YourField],4))


The existing question from the

 

[John Spencer]

I would try

CDate(Left([YourField],10))

or really paranoid

IIF(IsDate(LEFT([YourField],10)),CDate(Left([YourField],10)),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the same question as one of authors submitted about the problem in
converting the Timestamp format into a normal date format and I found answer
as listed below. However I couldn't get it worked ?? Please help.
My field is a text field from an imported file, and I am using Access 2003,
Select Query to run the date format on a new field from the table.

Answer:

If it is a text field then you need --
Val(Right(Left([YourField],7),2)&"/"&
Val(Right(Left([YourField],10),2)&"/"& Val(Left([YourField],4))


The existing question from the

I am having a problem converting a timestamp to a date format. the field
looks like this

2006-06-07-13.36.10.781340

and im trying to get it to say 6/7/2006

 

[KarenY]

Yes! it works, Thanks a lot, John,
Now I have another question after having viewed the date show on the table.
The file I imported was American Date from our Mainframe, i.e. mm/dd/yyyy,
e.g. 03/09/2007 and I am here in Europe, it will create the confusion for
other users.
Can I convert the date as dd/mmm/yyyy, e.g. 09MAR2007 at the same time when
running this : CDate(Left([YourField],10))
I am sorry, I should have put this question at the same time when I posted
the problem.

thanks
karen

I would try

CDate(Left([YourField],10))

or really paranoid

IIF(IsDate(LEFT([YourField],10)),CDate(Left([YourField],10)),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the same question as one of authors submitted about the problem in
converting the Timestamp format into a normal date format and I found answer
as listed below. However I couldn't get it worked ?? Please help.
My field is a text field from an imported file, and I am using Access 2003,
Select Query to run the date format on a new field from the table.

Answer:

If it is a text field then you need --
Val(Right(Left([YourField],7),2)&"/"&
Val(Right(Left([YourField],10),2)&"/"& Val(Left([YourField],4))


The existing question from the

I am having a problem converting a timestamp to a date format. the field
looks like this

2006-06-07-13.36.10.781340

and im trying to get it to say 6/7/2006

 

[KarenY]

Hi John, please disregard my question listed below, I've found out the answer
myself.
best regards,
karen

Yes! it works, Thanks a lot, John,
Now I have another question after having viewed the date show on the table.
The file I imported was American Date from our Mainframe, i.e. mm/dd/yyyy,
e.g. 03/09/2007 and I am here in Europe, it will create the confusion for
other users.
Can I convert the date as dd/mmm/yyyy, e.g. 09MAR2007 at the same time when
running this : CDate(Left([YourField],10))
I am sorry, I should have put this question at the same time when I posted
the problem.

thanks
karen

I would try

CDate(Left([YourField],10))

or really paranoid

IIF(IsDate(LEFT([YourField],10)),CDate(Left([YourField],10)),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the same question as one of authors submitted about the problem in
converting the Timestamp format into a normal date format and I found answer
as listed below. However I couldn't get it worked ?? Please help.
My field is a text field from an imported file, and I am using Access 2003,
Select Query to run the date format on a new field from the table.

Answer:

If it is a text field then you need --
Val(Right(Left([YourField],7),2)&"/"&
Val(Right(Left([YourField],10),2)&"/"& Val(Left([YourField],4))


The existing question from the
I am having a problem converting a timestamp to a date format. the field
looks like this

2006-06-07-13.36.10.781340

and im trying to get it to say 6/7/2006