Number Format in Database Results

[KLM]

HELP

I have a FP 2002 Web - submission form feeds an access '02 database - all this works just fine. Problem is when I use the FP database results to display the records from a query (same database) - all of the results display but the number formatting is incorrect for my needs. The formatting needs to be #.### which it is in access - but that formatting gets lost in the Db results region.

I have tried to edit the code but, as you know, that's not allowed. I have searched thge MS knowledge base as well as this forum but am unable to find a solution that I can get to work. PLEASE HELP - I need your assistance in detail. THANKS!

 

[Kathleen Anderson [MVP - FP]]

Take a look at the different number formatting options on this page:
http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#currency

 

[KLM]

Kathleen

Many Thanks for this link - great resource!

I still need a bit more help please. Where to I insert the SQL?? I assume in the HTML view of the DBRW in FP - but where in here?

The line of code which seems to pick the field I am trying to format is a webbot. When I insert your code in that line of text I either get an error message (nothing displays but asp error msg) or a double display of the number which is still not formatted correctly. The webbot line is

<!--webbot bot="DatabaseResultColumn" s-columnnames="Coach,Standing" s-column="Standing" b-tableformat="TRUE" b-hashtml="TRUE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Standing&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_Field(fp_rs,"Standing")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="17187" --></td

The name of the field from the Access Query is STANDING which is a numeric field. I'd like to get it formatted to display #.### -

Thanks for your continued help!

----- Kathleen Anderson [MVP - FP] wrote: ----

Take a look at the different number formatting options on this page
http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp#currenc

 

[Kathleen Anderson [MVP - FP]]

And I was just about to post the answer Glad you got working!

 

[Kathleen Anderson [MVP - FrontPage]]

Hi Bob:
Go into HTML view and look for a statement like this in the gray colored
code:

s-sql="SELECT
ProgramArea,RecordNum,Date,ItemNumber,SplitItem,Session,PA_SA,ActNumber,Sect
ion,Recipient,Municipality,FundsUse,Format(Total_Earmarking,'$#,##0.00') AS
DollarField1,Format(Allocation_Amount,'$#,##0.00') AS
DollarField2,Format(Previous_Allocation,'$#,##0.00') AS
DollarField3,Format(Balance_Unallocated,'$#,##0.00') AS
DollarField4,Any_Previous,Format(How_Much,'$#,##0.00') AS
DollarField5,FundNum,AgencyNum,SID,Project_Num,Description FROM
&amp;quot;Bond Commission Data Input&amp;quot; WHERE (Description LIKE
'%:escription::%') ORDER BY Date DESC, ItemNumber ASC"

This part is an example of the code change:

Format(Total_Earmarking,'$#,##0.00') AS DollarField1

HTH,

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[Bob]

I'm replying to my own request here because Kathleen very kindly lead me to the solution and I thought I would share it. Working with FrontPage 2002 and an Access 2000 database

If you are trying to format a number returned by Database Results then you need to tweak the SQL code. To access the editor for SQL then you need to evoke the Database Results Wizard. If you have been through the loop with this simply right click the Database Returns Area in your web page, click Database Returns Properties.., the wizard will start

Example of code

SELECT ProductName,LongDescription,format(UnitPrice9,'£#,##0.00') AS PSfield,Units FROM WebQuery WHERE (CategoryText = 'Cheese' AND Web = -1) ORDER BY WebOrder AS

UnitPrice9 was my Access field that returned unformatted. I had formatted the Access database until I was red in the face. The problem is not with Access but with asp Database Returns. Not enough zeros after the decimal point, no leading zero if less than one etc

To fix, start up the database results wizard, ensure your database connection is what you expect (Step 1) then at Step 2 click the custom button. You will see the SQL code. Play with it until you get the formatting as you would like it, £#,#00.00 was my choice (the code statement above was my working solution). Note that if you selected a field for your Database Returns table, in my case UnitPrice9, then after the AS statement you will see your AS field displayed, in my case PSfield, in the Database Returns Area on your FrontPage asp page

Hope it helps and thanks again Kathleen.