Extra Space in Date Format Formula

[tubbers]

I have a formula field which converts a datetime value to text. I have used the fomatting to have the day without any leading zeros, which works, except it leaves a blank space instead. Is there any way to get rid of that extra space so that it will match the other datetime fields in the report that have not been converted to text?

Table.Field1 is a DateTime datatype.

[b]@Date to Text[/b]

[COLOR=blue]ToText[/color] (Table.Field1},"M/d/yyyy") + ' ' + {@Admin Time}

[b]@Admin Time[/b]
[COLOR=blue]ToText [/color]([COLOR=blue]Time[/color] ({Table.Field1}), "HHmm")

This appears as:

4/ 2/2007 1634

When I directly format a DateTime field using the built-in Crystal formatting dialog box, I get

4/2/2007 1634

Note: I need to format Field1 in a formula as it is part of another formula that will add text based on the record's status.

Is this a bug in Crystal or am I missing something?

Crystal Reports XI, R1, on Windows XP SP2, using SQL Server MDB

Thanks!

 

[synapsevampire]

So you expect differing lengths for the same type of date.

This is very unusual, typiclayy people are trying to standardize, not add rules.

4/12/2007

would be a length of 9, 4/2/2007 would be a length of 8 even though it's the same supposed format.

Then you state:

Note: I need to format Field1 in a formula as it is part of another formula that will add text based on the record's status.

And you won't won't share the formulas involved, which is where the problem is.

Slow down, state facts and requirements and you'll find this simple to resolve.

-k

 

[tubbers]

OK, here's the majority of details that I know. If I am missing something specific you will need to tell me what it is.

I have a report which details the times a medication is scheduled to be given as well as when the actual medication was given. Each administration has a status, which could be ONTIME, CANCEL, EARLY, LATE or MISSED. If an administration has been marked as MISSED or CANCEL, then that need to be displayed as well. We're not concerned about ONTIME, EARLY, and LATE administrations at this time since you can tell from the Admin DTTM what had happened.

On the report, the first two columns in the detail section is the Scheduled DTTM and Admin DTTM. Scheduled DTTM is a field pulled directly from the database, no formulas involved, just formatting through Crystal Reports (Right Click, Format Field, Apply Custom Style...). The end result is this:

[b][u]Scheduled DTTM[/u][/b]
4/3/2007 0818

Right next to this column in the detail section is another field which is a combination of several forumulas. I shall provide them all for you:

[b]@Adm Date[/b]
If IsNull({Table.AdminDTTM}) Then 
      If {Table.AdminStat} = "MISSED " Then 
         "MISSED - " + {@Create Date to text}
      Else 
         If {Table.AdminStat} = "CANCEL" Then  
             "CANCELLED - " + {@Create Date to text}
         Else {@Create Date to text}
Else {@Admin Date to text}

[b]@Admin Date to Text[/b]
ToText ({Table.AdminDTTM},"M/d/yyyy") + ' ' + {@Admin Time}

[b]@Admin Time[/b]
ToText (Time ({Table.AdminDTTM}), "HHmm")

[b]@Create Date to Text[/b]
ToText ({Table.CreateDTTM},"M/d/yyyy") + ' '  + {@Create Time}

[b]@Create Time[/b]
ToText (Time ({Table.CreateDTTM}), "HHmm")

These are all of the formulas involved in this particular problem.

@Adm Date is the formula which is actually placed on the report (under the header of Admin DTTM). The Results display like this:

[b]
Scheduled DTTM     Admin DTTM[/b]
4/2/2007 0700       Missed - 4/ 2/2007 0827
4/2/2007 0800       4/ 2/2007 0827 
4/2/2007 0900       Cancelled - 4/ 2/2007 0827      
4/2/2007 1000       4/ 2/2007 0827

As you can see,under the heading Admin DTTM, there is an extra space in the day placeholder when the datetime is formatted within a formula. This is not so if you format a datetime field within the Crystal Format Field function (field under the Scheduled DTTM heading).

And actually, I am trying to standardize the look of the report. I do not want the extra space and am trying to figure out why it shows up and how to remove it. so that my report looks like this:

[b]
Scheduled DTTM     Admin DTTM[/b]
4/2/2007 0700       Missed - 4/2/2007 0827
4/2/2007 0800       4/2/2007 0827
4/2/2007 0900       Cancelled - 4/2/2007 0827     
4/2/2007 1000       4/2/2007 0827

 

[brendasql]

Hi,
This maybe too simple to work, but give TRIM a try.

example - Trim(ToText ({Table.AdminDTTM},"M/d/yyyy")) + ' ' + {@Admin Time}

Maybe you'll get lucky and it will work.

Brenda

 

[tubbers]

Sorry, that didn't work. I think that solution would only work to trim extra spaces off the end of the string.

 

[lbass]

I too have noticed this behavior. I vote for "bug".

-LB