Question Text Column to be converted to Date/Time

[venkateshr28]

Hello Experts,

This is my 2nd post in this forum regarding MS access 2007 help.

Unfortunatelly i am working on a senstive information pleae excluse me for not posting the DB here.

I am stuck need help on converting a text column to Date/Time format please

I Have imported this table from excel which has StartTime as column, when its imported to MS access the column is set to text by default, if i change this manually to Date/time column itself is becoming blank in the table.

I have tried using DateValue function but error as Type Mismatch
i have tried Format function as well but didnt work. Can anyone suggest please

Below is my query in which i need to convert StartTime filed to Date/Time format:banghead:

SELECT A_B_New.[Bank ID], A_B_New.[Staff Name], A_B_New.Date, A_B_New.StartTime
FROM A_B_New
WHERE (((A_B_New.Type)="Available Hours"));

 

[theDBguy]

Hi. Welcome to the forum. Can you show us some sample data for the date/time field you're trying to convert?

 

[MajP]

the CDATE function converts text into date. It is pretty powerful if it looks anything like a common date time format. If your format is too non standard then you may need a custom function using TIMESERIAL and some string manipulation to pass the correct arguments to TIMESERIAL

 

[venkateshr28]

Hello TheDbGuy,

I have attached a sample picture of how Starttime column looks like.

Hi Majp,

I will try your suggestions too and keep u guys updated in a while.

Appreciate your time...

 

[Cronk]

Your imported time string includes milliseconds which Access can't handle. Cdate will error out.


If you are happy to accept rounding down the seconds then you can simply truncate the string to remove the milli seconds

cdate(left(TimeStart, len(TimeStart,)-4))

 

[venkateshr28]

Hi Cronk,

Perfect. It worked like charm. Thank you so much. You made my day...