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.
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.
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.
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.
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.
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
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?
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.
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?
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
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 ???
