Convert to Datetime and Right()

[dickohead]

Hey Guys,

Having some trouble doing a couple of things using both Data Definition Language and a Right() Query.

What I am trying to do is convert this field:
August 17, 2006 3:56 PM
into this
15:56:00

Using this:
Right([Travel Date/Time To],InStr([Travel Date/Time To],",")-1)

Now because the time part in my first field can vary from being 3:56 PM to 12:56 PM Right() is only getting those where the time has two digits.

I tried to use DDL to convert my field to DATETIME but cannot convert two columns in one SQL statement.

Can anyone help me out with this? Sorry if the details are sketchy but I'm being kicked out of the office (after 5:00pm you see!).

Cheers.

 

[gemma-the-husky]

In Access, you don't need to do anything like that. As long as your date can be read as a date by access, it will be treated as any other date. In which case, it can be displayed in many ways.

eg in a text box, format the the text box as "long time". (ie in the text box properties)

in an expression, format it as format(mydate,"long time")

any good?