Currency Format in a Listbox Column

I’ll gonna test CompareRows

Of course!!! CompareRows solves this problem. Also @Wayne Golding was right, I need to delete my code existent on Celltextpaint.

But now I need to recode all my stuff like:

  1. draw the numbers in red when are equals to zero or negative numbers.
  2. when a certain cell says something replace text
  3. and put currency format like: $1,532.00 instead of 1532.00

PLAYING SOMETHING WTIH THE CODE I RECOVERED ALL MY PAINT TEXT FUNCTIONS EXCEPT FOR FORMATTING AS CURRENCY.

THIS IS THE CODE THAT I USED ON CELLTEXTPAINT:

me.Cell(Row,Column) = format(VAL(me.CellTAG(Row,Column)),"-\\$###,##0.00")

If i use it, the column required get currency format.
BUT If i use that my CompareRows don’t work.

ERGO, If i delete that code, CompareRows works flawlessly, but numbers unformatted

to deal with currencies, I first convert it to string, then to double
if you use the recordset.currencyvalue, it may perhaps work in english systems, but not on my french one …
so I use recordset.stringvalue to get something like “$1,234.56” which is stored as MONEY on my postgresql database.
then I use this method to convert to a real currency.
then you can use it like you want as a double you format it the way you want.

[code]Function ToCurrency(extends aString as String) As Currency
dim res as Currency = 0
dim ch,ch2,c as string

ch = Trim( aString)
ch = ReplaceAllB( ch, " “, “”) ’ supprime les espaces comme dans “- 1,23”
if ch.Contains(”,") and ch.Contains(".") then
ch = ReplaceAll( ch, “,”, “”)

elseif ch.Contains(",") then
’ on a une virgule il faut la remplacer
ch = ReplaceAll( ch, “,”, “.”)
else
’ on a un point seulement deja
end if
’ deletes the non-numeric chars like $ or € or £
ch2 = “”
for i as Integer=1 to Len(ch)
c = Mid(ch,i,1)
if IsNumeric© or c="," or c="." or c="-" then
ch2=ch2+c
end if
next

res = Val( ch2)

Return res

End Function
[/code]

[quote=275364:@Jean-Yves Pochez]to deal with currencies, I first convert it to string, then to double
if you use the recordset.currencyvalue, it may perhaps work in english systems, but not on my french one …
so I use recordset.stringvalue to get something like “$1,234.56” which is stored as MONEY on my postgresql database.
then I use this method to convert to a real currency.
then you can use it like you want as a double you format it the way you want.

[code]Function ToCurrency(extends aString as String) As Currency
dim res as Currency = 0
dim ch,ch2,c as string

ch = Trim( aString)
ch = ReplaceAllB( ch, " “, “”) ’ supprime les espaces comme dans “- 1,23”
if ch.Contains(”,") and ch.Contains(".") then
ch = ReplaceAll( ch, “,”, “”)

elseif ch.Contains(",") then
’ on a une virgule il faut la remplacer
ch = ReplaceAll( ch, “,”, “.”)
else
’ on a un point seulement deja
end if
’ deletes the non-numeric chars like $ or € or £
ch2 = “”
for i as Integer=1 to Len(ch)
c = Mid(ch,i,1)
if IsNumeric© or c="," or c="." or c="-" then
ch2=ch2+c
end if
next

res = Val( ch2)

Return res

End Function
[/code][/quote]
Hi @Jean-Yves Pochez, Did you try to tell me that You have formatted text with $ or € or £ in CellTextPaint?
And In order to get it Work the Sorting, you invoke that function after to call the normal procedure to Sort all Numbers?

And doing this, Have formatted Numbers as Currency but also having Sorting?
Thanks

I remove the $ £ € in the beginning of the string the currency gives me, then treat it as a double to sort it correctly.
recordset.idxfield(i).currencyvalue gives me an empty value
recordset.idxfield(i).stringvalue gives me $ and the correct value on my french mac os 10.9 system.
again it’s for a postgresql database.

[quote=275485:@Jean-Yves Pochez]I remove the $ £ € in the beginning of the string the currency gives me, then treat it as a double to sort it correctly.
recordset.idxfield(i).currencyvalue gives me an empty value
recordset.idxfield(i).stringvalue gives me $ and the correct value on my french mac os 10.9 system.
again it’s for a postgresql database.[/quote]
Ok, but .Contains???, exists this property for String

You said in the function:

if ch.Contains(",") and ch.Contains(".") then

But Xojo says me that doesn’t exist Contains

Try InStr instead. Contains is not a function.

oups sorry ! missing function from standard Xojo …

[code]Function Contains(extends astring as String, insideString as String) As Boolean
'---------------------------------------------------------------------------------
’ Description : retourns true if the string contains insideString
'---------------------------------------------------------------------------------
dim res as Boolean = False

if len(astring)>0 then
res = ( InStr( astring, insideString)>0)
end if

return res
End Function
[/code]

[quote=275497:@Jean-Yves Pochez]oups sorry ! missing function from standard Xojo …

[code]Function Contains(extends astring as String, insideString as String) As Boolean
'---------------------------------------------------------------------------------
’ Description : retourns true if the string contains insideString
'---------------------------------------------------------------------------------
dim res as Boolean = False

if len(astring)>0 then
res = ( InStr( astring, insideString)>0)
end if

return res
End Function
[/code][/quote]
Thanks @jean-Yves Pochez you were right!!!, just replacing $ and comma in CompareRows and It works flawlessly.

I find it better to store the Currency/Double value in the CellTag property, and display the formatted text in the Cell property. In that way you do not have to convert back and forth.

In any sort function use the CellTag property and any get data back from the cell use the CellTag property. I think this would be faster as well.

Simon.