Convert to 2 decimal places?

Hi,
I have made software to keep track of my income and expenses.
I am using a ListBox (populated from a SQLite database), which contains a column for income amounts, and a column for expense amounts.

I have run into a small problem and am not sure what part I need to change.

I have a textfield where I enter an amount (example 12.50).
This value is then saved to my database (the DataType is “TEXT”).

The problem is that I need whatever is entered into the TextField to be saved to the database with at least 2 digits before and after the period.

Currently, if I type in 0.5, it gets saved to the database as exactly that - 0.5, but I need it to be automatically converted and saved as 00.50
Likewise, if I type in 6.50, I need it to get saved to the database as 06.50.
Also, if someone types in 13.5, I need it to get saved to the database as 13.50.

Hopefully that made sense - I need there to always be at least 2 digits before and after the period.

Can someone please help me with this, as my math knowledge is only rudimentary.

Thank you all in advance.

I’m thinking the format function is what you are looking for here.

Off the top of my head, convert the input to a double and then use format to get it back to a string in the format you are looking for.

Don’t have exact code to share at the moment, but you should be able to figure it out from the language reference.

You are better off to use the CURRENCY datatype… no rounding etc required.

for DISPLAY purposes…

msgbox format(myvalue,"###,##0.00")

Thanks guys.

Dave,
If I change the database DataType to Currency - I am presuming 3.50 or 3.5 would get saved as 03.50, and that 3456 would get saved as 3,456.00 (is that right)?

I would expect that there will not be any leading zero in the database. The format function will add it when necessary on the UI.

It is my opinion that you should not attempt to save your data in the database as formatted data. Formatting is often a question of localization or personal preference. Keeping the data in the database as an optimal type will make later customization of the user interface easier to achieve.

using CURRENCY datatype does not change the FORMAT of the data… just insures that stay accurate to 2 decimal places…

3.5 is 3.5
3.501 is 3.5

should be:
msgbox format(myvalue,"-00,.00")
The minus symbol should be added to show the sign and 2 zero’s before the decimal point to have 2 digits before the decimal seperator. Also the comma before the dot will act as a thousand seperator.
The ‘#’ before the zero’s are not needed.

Ok,
let me try from a different angle:

How can I check to see if the last 3 digits are a period, followed by any 2 digits?

For Example:
If the user enters 327 (three hundred and twenty seven dollars), I need to append .00 to the end, so that the figure 327.00 is saved to the database. But, if the user also enters the cents amount, such as 327.50 - DO NOT append .00 to the end.

I only need to append .00 if only the dollar amount is entered by the user.

Thanks guys.

what datatype are you saving in the database?
The only time you “care” about trailing zeros will be when you DISPLAY the number… not when you STORE it
If you set the database Dataype to CURRENCY it will maintain the ACCURACY (ie. you won’t have 1.009999999)
and use FORMAT as suggested above to DISPLAY it…

Numeric values should normally NOT be stored as Text/String types unless you have a compelling reason to do so.

rs.field(“money”).currencyvalue=3.5

s=format(rs.field(“money”).currencyvalue,“0.00”)

And the above WILL work with your current SQLite… as it is very loosely type to begin with.

If the user can enter various values for their income, such as :

3.50
45
127
350.45

What Datatype should I be using?
If I use CURRENCY, as you advised, then if the user for some strange reason entered 3.5 (meaning $3.50c), the database would store the value as 3.5

I use the following code to display the data in the ListBox, and have no idea how to modify it to append any cent amounts (if necessary):

Window1.PopulateAccounts(lb_accounts,db,"select ACRef, Date, Description, Income, Expenses from Accounts order by Date desc")

Richard, you seem to be having a mental block here. If the user enters 3.5 instead of 3.50 they both have the exact same value when saved to the database. And both will show up as 3.50 in the listbox if you use the proper Format command. 3.5 = 3.50 so far as dollars and cents go.

Harrie,
I agree - I do have a mental block on this particular problem, and it’s driving me nuts :slight_smile:

So if I use the CURRENCY datatype, it’s ok if the user enters 3.5 BUT, I am using the code below to load the data into the ListBox:

Window1.PopulateAccounts(lb_accounts,db,"select ACRef, Date, Description, Income, Expenses from Accounts order by Date desc")

I am using 1 line of code to display all the data contained in the database in the ListBox, and therefore I can’t understand how I use the format command to display it with the cents appended?

Does that make more sense? :slight_smile:

Richard, the format function assigns a predetermined template (the “format”) to the string representation of a value. You define a template, say “###,###.00” that will be applied to the string representation. The underlying value remains unchanged. The format definition only tells the format function to manage the digits in the way you indicated. In this example, the format string tells the function to insert a comma as a thousands separator, and to complete the decimals with trailing zeros if necessary, up to 2 places. But, again, the underlying value remains unchanged. Here, 3.5 will be represented as 3.50. But it is still 3.5. It is all the same, just the representation is affected.

You may have to adapt your code in order to take advantage of the format function, or perhaps you can apply it directly to the monetary values in your code. Of course, you may have to change some of the details of your PopulateAccount method, or of the listbox. Maybe you need to experiment a bit with the format function in a simpler prototype program in order to appreciate better how to use it.

[quote=79216:@Richard Summers]I use the following code to display the data in the ListBox, and have no idea how to modify it to append any cent amounts (if necessary):

Window1.PopulateAccounts(lb_accounts,db,“select ACRef, Date, Description, Income, Expenses from Accounts order by Date desc”)
[/quote]

This is not enough. Please post the complete PopulateAccounts method code.

Thank you guys.

The PopulateAccounts method is as follows (inherited code - not originally made by me):

lb_accounts.deleteAllRows // run the query rs=db.sqlSelect(sql ) if rs = NIL then db_error exit sub end if // populate the listbox while not rs.eof lb_accounts.addRow ""// add a new row for i as integer = 1 to rs.fieldCount// add data to columns lb_accounts.cell(lb_accounts.lastIndex, i-1) = rs.idxField(i).stringValue next rs.moveNext// move to next record wend

The code to populate the ListBox is:

PopulateAccounts(lb_accounts,db,"select ACRef, Date, Description, Income, Expenses from Accounts order by Date desc")

I therefore, need to somehow modify either of these 2 code segments to include the cent values?

Thank you all in advance for your patience.

lb_accounts.cell(lb_accounts.lastIndex, i-1) = format(rs.idxField(i).CurrencyValue,"-0.00")

Dave,
I only need to format columns 3 and 4 in this way - whilst columns 0,1 and 2 should remain unformatted.

So put in conditional code that controls that.

select case i-1
case 0,1,2
 lb_accounts.cell(lb_accounts.lastIndex, i-1) = rs.idxField(i).stringValue
case 3,4
lb_accounts.cell(lb_accounts.lastIndex, i-1) = format(rs.idxField(i).CurrencyValue,"-0.00")
end select

ALMOST THERE !

It works, with one exception:

If the user enters 3 it formats to 3.00 (exactly what I need)
If the user enters 3.5 it formats to 3.50 (exactly what I need)
If the user enters 399 it formats to 399.00 (exactly what I need)

BUT - if the user enters 1,299 - it formats to 1.00 ???

So near, yet so far :slight_smile: