Show it as real time 1

[WB786]

I am working with a third party Orcale database that stores time in the following text format: 070000

I need to format this time field as: 07:00:00 AM.

Can this be done?

Thank you,

WB

 

[dhookom]

Substitute your field name for "070000" in this expression:
[tt]
timevalue(Left("070000",2) & ":" & Mid("070000",3,2) & ":" &Right("070000",2))[/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WB786]

Do I put this in the Format property of the field? Because there it is not working.

Thanks,

WB

 

[dhookom]

Use this as the expression in a query or the control source of a text box. It simply converts your text to a real time which can be formatted as you wish.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WB786]

I am getting expression is too complex in the query and in the control source I am getting #Error.



WB

 

[dhookom]

This isn't too complex unless there is something else in your query that we don't know about. Did you use an "=" sign in front of the expression? What is the name of the text box? What did you enter as the exact control source?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WB786]

There are three tables involved in the query: Table A is joined to Table B at three different fields equally. Table A is also joined equally to table C only at CustNo field.
Some fields from all tables are being used in the query.

From the query:
Expr1: TimeValue(Left("RSJE_DEL_START_TIME",2) & ":" & Mid("RSJE_DEL_START_TIME",3,2) & ":" & Right("RSJE_DEL_START_TIME",2))

From the Control Source in the report text box called RSJE_DEL_START_TIME:
=TimeValue(Left("RSJE_DEL_START_TIME",2) & ":" & Mid("RSJE_DEL_START_TIME",3,2) & ":" & Right("RSJE_DEL_START_TIME",2))

Keep in mind the query works fine but will not if the expression is inserted.

Thank you,

WB

 

[dhookom]

Don't place quotes around the field name. Try:
RealStartTime: TimeValue(Left(RSJE_DEL_START_TIME,2) & ":" & Mid(RSJE_DEL_START_TIME,3,2) & ":" & Right(RSJE_DEL_START_TIME,2))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WB786]

That DID it. Thank you for all the help!!!!

WB