Inserting a ":" in-between numbers at runtime

[mgkSHA]

Hello:

I have Crystal Report that is functioning within a VB 6.0 application. This application is getting information from a large backend Access Database which is dumped Phone Switch information every day. The "Call time" field on the phone switch is a text field, so it dumps into the table four numbers such as 1434 (military time) instead of AM/PM format or 07:21 Military Format. This format is a pain, but currently we are not going to change it for various tech reasons.

Since this field is not a date field, I have had to do a lot of code formating SQL in VB 6.0 in order to transfer the data to a date. For Crystal, I know the SQL I have used for VB will not work - - it does not follow Crystal Syntax. My question is, how can I get a ":" inbetween two numbers so that my Call Time field reads 14:34 instead of 1434. I have tried formulas like this but have not been successful:

{ado.CallTime} "is equal to" Left({ado.CallTime},2) & ':'& Right({ado.CallTime}, 2).

Can anyone help me make this 4 digit field look like this 2 characters : 2 characters. Any advice would be great.

Martin K

 

[Naith]

Hey Martin,

The Picture function is for exactly this type of thing:

Picture({ado.CallTime},"xx:xx&quot

All the best,

Naith

 

[mgkSHA]

Naith:

Where do I put this picture formula? Do I right click the actual Call Time field and put this in the "select expert", or do I put this in the SQL statment? Or somewhere else.
Thanks

Martin

 

[mgkSHA]

{ado.CallTime} = Picture({ado.CallTime}, "xx:xx&quot

Naith:

I put this code in the Call Time field "select expert" formula, it does not work...it returns a blank report...please advise.

 

[Naith]

No, Martin - all Picture does is change the way a field looks. The actual field itself, when passing to and from the database is still a 'xxxx' string.

Create a formula, as below, and use the formula to display in your report instead of {ado.CallTime}.

//{@Formula Starts}
If Length({ado.CallTime}) = 3
Then Picture({ado.CallTime},"x:xx&quot
Else
Picture({ado.CallTime},"xx:xx&quot
//End

I put in a length checker in the beginning of the formula, because I don't know if your morning strings are 845 or 0845.

Naith

 

[mgkSHA]

Naith:

Where do I put this formula? Again, I am a little confused. I understand, this code does not go in the select expert of the Call Time field itself...then where do I put it? Where should I build this formula, what area of the report.

 

[mgkSHA]

Naith:

Nevermind, I figured it out...I put the @formula command on the report in place of the calldate field..thanks for the advice