Spooky's Databases > Advanced Tips > Change the Outputted Value of a Column

Often we need to customize the text or values that are the result of a query. There's many reasons for doing that, so we will try and make it easier for you to understand how!

• Retain formatting of carriage returns in memo fields
• Format the value (date, currency, text etc) 
• Add if / then / else statements

Quite often, in the discussion forum, you will hear us say "Modify the gray code!"
What this means, is the gray code that is used to display the database value.

Here's the example again.

We display a value using this FrontPage generated code :

<tr>
<td>

<!--webbot bot="DatabaseResultColumn" startspan
s-columnnames="ProductID,ProductName,SupplierID,CategoryID
,QuantityPerUnit,UnitPrice,UnitsInStock,
UnitsOnOrder,ReorderLevel,Discontinued"
s-column="ProductID"
b-tableformat="TRUE"
b-hasHTML="FALSE"
clientside local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ProductID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ProductID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" -->

<%=FP_FieldVal(fp_rs,"ProductID")%>

<!--webbot bot="DatabaseResultColumn" i-CheckSum="17683" endspan -->

</td>
</tr>

The code can actually be shortened to this :

<tr>
<td>
<%=FP_FieldVal(fp_rs,"ProductID")%>
</td>
</tr>

Not only will your page loading be sped up, its also a lot easier to read the code! 

Now the asp code that is left, can be modified as any normal asp can be.
Here's a list of formatting codes that are used with VBScript

Here are some common ones that we use, and will give you an idea of the format required when using any of the other functions from the link above.

Formatting Memo fields
This will reinsert the line breaks that appear in your text box but get lost when displayed by the DRW

<%=replace(FP_FieldVal(fp_rs,"Product"),chr(10)," <br>")%>

Formatting Dates
Different date time formats can be achieved using the values 1,2,3,4

<%=FormatDateTime(FP_FieldVal(fp_rs,"TimeField"),2)%>

Formatting Currency

<%=FormatCurrency(FP_FieldVal(fp_rs,"CurrencyField"),2)%>

Formatting Numbers

<%=FormatNumber(FP_FieldVal(fp_rs,"NumberField"),2)%>

For specific formatting options, check the Microsoft documentation above