Format Listbox Number

Hi All,

Picking Xojo up again after several years and trying to shake off the rust. I have a list box i am populating using the results of a SQL query and am struggling with the formatting / display of some of the numeric values.

[code] dim db as new ODBCDatabase
dim strSQL as string
lstWO.DeleteAllRows
db.DataSource=""+uDSN+""
if db.connect then
strSQL = “SELECT wo.WONo,wo.SHIPNAME,wo.UnitNo,isnull(wo.servicevan,’’)as servicevan, isnull(wo.servicezone,’’) as servicezone,isnull(CONVERT(VARCHAR(10),wo.ShopDateIn,101),’’) as ShopDateIn,isnull(CONVERT(VARCHAR(10),wo.ShopEstimatedCompletionDate,101),’’) as ShopEstimatedCompletionDate,”_
+“isnull(wo.shopquotehours,0) as ShopQuoteHours,wo.ServicePriority”_
+" as color,convert(numeric(10,2),(isnull(wl.SumHours,0))) as SumHours FROM WO left outer join (select wono,isnull(SUM(hours),0)as SumHours from wolabor group by wono) wl on wo.wono=wl.wono"_

  • " where wo.Disposition=1 AND wo.ServicePriority <> ‘Complete’ AND SALEDEPT in “+DeptList+” AND SALEBRANCH = ‘" + str(BranchID) + "’ order by WONO"
    Dim rs As RecordSet = db.SQLSelect(strSQL)
    if rs <> nil then
    While Not rs.EOF
    lstWO.AddRow(rs.Field(“WONO”).StringValue, rs.Field(“ShipName”).StringValue, _
    rs.Field(“UnitNo”).StringValue, rs.Field(“ShopDateIn”).StringValue, rs.Field(“ShopEstimatedCompletionDate”).StringValue,_
    rs.Field(“Color”).StringValue,rs.Field(“ServiceZone”).StringValue,rs.Field(“ServiceVan”).StringValue,rs.Field(“ShopQuoteHours”).StringValue,rs.Field(“SumHours”).StringValue)
    [/code]

Specifically for the 2 fields in the ResultSet that return hours I am trying to get the list box to display 2 digits to the right of the decimal. This works if the underlying amount is something like 1.25 hours the listbox correctly displays 1.25, but if the SQL returns a zero decimal number like 4.00 hours the listbox is only showing 4. I suspect I am losing something in the conversion to the StringValue but I am struggling with how to get every row to show with 2 decimal places.

read the value into a variable and format it

s=format(rs.field("nameoffield").doublevalue,"##,##0.00")

and then use “s” in the Addrow

Dave,

Thank you so much, that was exactly what I was looking for!

Appreciate the help.

Joe