change data type

  • Thread starter Thread starter William McNeill
  • Start date Start date
W

William McNeill

I have a query that I have created which contains a different format on the
date and time than what I would like. Using "CDate" in the value did not
work.

My problem is that in the table the dates have been inputted as for
instance: 31.12.2008, in a DMY format and using a period instead of a slash.
I would like to convert this to a MDY and convert it from text to date data
type.

Similar situation with the time. It is in the table as 13:00:00.000 with a
decimal showing up to 1/1000 second. I would like to convert this data type
from text to time and the format to be 13:00:00.

The name of the 2 fields are Dte and Tme. Access 2007. Thanks!!!!
 
TheDate: CDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" & Mid([Dte],7,4))

The above assumes two things:
1. That the date format is always the same including leading zeros.
2. Every record in that field can be evaluated as a proper date.
CDate will cause an error if even one record is something like 30.02.2009.

If you think that there may be a problem, you could use something like below
to present a bogus date, 1/1/1950, if the string can't be properly evaluated
as a date.

TheDate: IIf(IsDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" &
Mid([Dte],7,4)), CDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" &
Mid([Dte],7,4)), #1/1/1950#)

TheTime: CDate(Left([Tme],8))

This will give you a proper 1:00:00 PM time. You could format it to look
differently if you wish.
 
Thank you very much Sir. This worked great!!

Jerry Whittle said:
TheDate: CDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" & Mid([Dte],7,4))

The above assumes two things:
1. That the date format is always the same including leading zeros.
2. Every record in that field can be evaluated as a proper date.
CDate will cause an error if even one record is something like 30.02.2009.

If you think that there may be a problem, you could use something like below
to present a bogus date, 1/1/1950, if the string can't be properly evaluated
as a date.

TheDate: IIf(IsDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" &
Mid([Dte],7,4)), CDate(Mid([Dte],4,2)& "/" & Left([Dte],2)& "/" &
Mid([Dte],7,4)), #1/1/1950#)

TheTime: CDate(Left([Tme],8))

This will give you a proper 1:00:00 PM time. You could format it to look
differently if you wish.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


William McNeill said:
I have a query that I have created which contains a different format on the
date and time than what I would like. Using "CDate" in the value did not
work.

My problem is that in the table the dates have been inputted as for
instance: 31.12.2008, in a DMY format and using a period instead of a slash.
I would like to convert this to a MDY and convert it from text to date data
type.

Similar situation with the time. It is in the table as 13:00:00.000 with a
decimal showing up to 1/1000 second. I would like to convert this data type
from text to time and the format to be 13:00:00.

The name of the 2 fields are Dte and Tme. Access 2007. Thanks!!!!
Click to expand...
Click to expand...
 
Back
Top