Time in text field

[Former Member]

Hi!

I have a call start time field in MySQL. The field type in the database is text. When I browse the data in Access I can see that each record has a valid time in 24hr time format (ex. 13:01:00). The field type in Access shows up as text. When I use this field in Crystal, it shows up as a Time data type. I guess because Crystal is smart enough to look at the data and determine it's all formatted like time data. The problem is that when I display this field on my report, it shows up as 12:00AM or 0:00:00 depending on which format I pick. All the records show up this way even though I have verified each record has a valid time.

Is there anyway I can have Crystal see the data as Text and not try to convert it to Time? At least if it's Text, I can see what the times really are and then use a formula to convert it to Time if want to.

PS: If I make an ODBC link in Access to the database and then reference the table via an Access connection in Crystal, I can see the times and Crystal shows they are a Text field. I really don't want to go through Access for this particular report if I can avoid it. I'm sure it will slow things down considerably by making a passthrough connection via Access. I've done that in the past but this table has quite a bit of data.

I'm using Crystal XIr2.

Thanks!

Cyndi

[Former Member · Accepted solution]

Hi Brian,

Here is the formula I used

totext({calls_detail.CallStart}, "hh:mm tt");

I tried changing the format on the report of the original database field too. Each format just gave me some variation of 12:00:00 (either 0:00:00 or 12:00am).

It's like Crystal knows it's a time field but then when it brings it in, it converts it to 12:00 thinking it's not valid or something.

Thanks,

Cyndi

[Former Member · Accepted solution]

ctime(13,01,00) = 1:00:00PM

[Former Member]

Hi Brian,

I added a formula field and like you said, it shows 12:00 am. How do I then change it to show the actual time?

Thanks,

Cyndi

[former_member292966]

Hi Cyndi,

Now I'm confused. It should have shown you the time saved into your time field. Can you copy and paste your formula here so I can see if you did it correctly?

Thanks,

Brian

[former_member292966]

Hi Cyndi,

I realized there may be an easier way to do this.

When you drop the Time field onto your report, right-click it and select Format Field. You can then select the time format you want this field to be displayed as. Regardless of what the regional settings are on the machine, it will always display this way.

Good luck,

Brian

[former_member292966]

Hi Cyndi,

I would recommend creating a formula and using the ToText function like:

ToText ({TABLE.Field}, "hh:mm tt");

This will show it as a text as 12:00 am but you can change the format to however you want.

Good luck,

Brian