I often find requests for help in formatting strings. Just remember you can use your r vb formatting syntax here be it just text, date or numbers.
The following are some samples in formatting Strings
Formatting a telephone no:
Formatting Date and Time
Examples of applying a format string to date/time values are shown in the following table.
Format Output
Format(Now,"D") Friday, October 12, 2007
Format(Now,"d") 10/12/2007
Format(Now,"T") 6:37:23 AM
Format(Now,"t") 6:37 AM
Format(Now,"F") Friday, October 12, 2007 6:37:23 AM
Format(Now,"f") Friday, October 12, 2007 6:37 AM
Format(Now,"g") 10/12/2007 6:37 AM
Format(Now,"m") October 12
Format(Now,"y") October, 2007
If the database field is numeric then
=Format(12345678,"(###)##-###")
or
=Format(Fields!phonenumber.value,"(###)##-###")
If the field is of datatype String then you need to convert it =Format(Cdbl("12345678"),"(###)##-###"))
=Format(12345678,"(###)##-###")
or
=Format(Fields!phonenumber.value,"(###)##-###")
If the field is of datatype String then you need to convert it =Format(Cdbl("12345678"),"(###)##-###"))
Formatting Date and Time
Examples of applying a format string to date/time values are shown in the following table.
Format Output
Format(Now,"D") Friday, October 12, 2007
Format(Now,"d") 10/12/2007
Format(Now,"T") 6:37:23 AM
Format(Now,"t") 6:37 AM
Format(Now,"F") Friday, October 12, 2007 6:37:23 AM
Format(Now,"f") Friday, October 12, 2007 6:37 AM
Format(Now,"g") 10/12/2007 6:37 AM
Format(Now,"m") October 12
Format(Now,"y") October, 2007
1 comment:
A good practice is to use a custom assembly where you implement custom formatting functions. One of the reasons is the maintainability. You change the code only once to have the format changed changed in all RDLs.
E.g.
public class Formatting
{
private static readonly DateTime minDate = new DateTime(1900, 1, 1);
/// <summary>
/// Performs formatting of timeSpan values in default format [-][d.][hh:MM:ss]
/// </summary>
/// <param name="input">Any compatible value</param>
/// <returns>Formatted timespan string, or an empty string if the value is NULL or cannot be coverted</returns>
public static string FormatTimeSpan(object input)
{
try
{
if (input == null) return string.Empty;
else if (input is TimeSpan)
{
TimeSpan ts = (TimeSpan) input;
return (ts.Days != 0 ? string.Format("{0}.", ts.Days) : string.Empty)
+ string.Format("{0:00}:{1:00}:{2:00}", ts.Hours, ts.Minutes, ts.Seconds);
}
else if (input is SqlDateTime)
{
SqlDateTime sqlDt = (SqlDateTime) input;
if (sqlDt.IsNull) return string.Empty;
return FormatTimeSpan(sqlDt.Value - minDate);
}
else if (input is SqlDecimal)
{
SqlDecimal sqlDecimal = (SqlDecimal)input;
if (sqlDecimal.IsNull) return string.Empty;
return FormatTimeSpan(TimeSpan.FromDays(Convert.ToDouble(sqlDecimal.Value)));
}
else if (input is SqlDouble)
{
SqlDouble sqlDouble = (SqlDouble)input;
if (sqlDouble.IsNull) return string.Empty;
return FormatTimeSpan(TimeSpan.FromDays(sqlDouble.Value));
}
else if (input is DateTime)
{
DateTime dt = (DateTime)input;
return FormatTimeSpan(dt - minDate);
}
else if (input is int || input is Int16 || input is SqlInt32 || input is SqlInt16)
{
int ms = Convert.ToInt32(input);
return FormatTimeSpan(TimeSpan.FromMilliseconds(ms));
}
else return FormatTimeSpan(TimeSpan.FromDays(Convert.ToDouble(input)));
}
catch
{
return string.Empty;
}
}
}
------------
Ruslan Urban
Post a Comment