I have always used an integer value for a money type, then simply divide by 100 for display. This avoids fractional penny values occurring.
Although mysql supports a decimal type, I've never found a way of using it in Gambas.
Any ideas?
advice needed re mysql decimal field type
-
- Posts: 195
- Joined: Tuesday 26th September 2017 3:17pm
- Location: NW England
-
- Posts: 195
- Joined: Tuesday 26th September 2017 3:17pm
- Location: NW England
Re: advice needed re mysql decimal field type
Create a field of type DECIMAL(6,2) using an sql statement .
This then shows as type 'float' in Gambas but is always has only 2 decimal places.
This then shows as type 'float' in Gambas but is always has only 2 decimal places.
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: advice needed re mysql decimal field type
You can convert a float to a currency by using Round(
Value = Round ( Number [ , Digits ] )
Rounds a number to its nearest integer, if Digits is not specified.
If Digits is specified, rounds to 10 ^ Digits.
Examples
PRINT Round(Pi, -2)
RunPlay
3.14
PRINT Round(1972, 2)
RunPlay
2000
Value = Round ( Number [ , Digits ] )
Rounds a number to its nearest integer, if Digits is not specified.
If Digits is specified, rounds to 10 ^ Digits.
Examples
PRINT Round(Pi, -2)
RunPlay
3.14
PRINT Round(1972, 2)
RunPlay
2000
Cheers - Quin.
I code therefore I am
I code therefore I am
-
- Posts: 195
- Joined: Tuesday 26th September 2017 3:17pm
- Location: NW England
Re: advice needed re mysql decimal field type
Thanks Quincunxian that is useful.
With accounting and financial projects its important to avoid unwanted fractions of a penny. For this reason I have always used integers to represent money values, dividing by 100 to display the true value. This way there is never a problem with penny fractions.
Recently I've tried the sql DECIMAL(x,y) field. Although Gambas sees the database field (column) as type 'float' an attempt to change the sign of a value (e.g. hResult!DebitValue) results in an error (got STRING, wanted FLOAT). The work around is to move the value into a float variable then change the sign.
Also a simple arithmetic functions on float values throws up fractional penny vales. This is where ROUND helps.
I see there is a functions _DataTypes.SignedDecimal (gb.mysql) but I've no idea how to use it.
With accounting and financial projects its important to avoid unwanted fractions of a penny. For this reason I have always used integers to represent money values, dividing by 100 to display the true value. This way there is never a problem with penny fractions.
Recently I've tried the sql DECIMAL(x,y) field. Although Gambas sees the database field (column) as type 'float' an attempt to change the sign of a value (e.g. hResult!DebitValue) results in an error (got STRING, wanted FLOAT). The work around is to move the value into a float variable then change the sign.
Also a simple arithmetic functions on float values throws up fractional penny vales. This is where ROUND helps.
I see there is a functions _DataTypes.SignedDecimal (gb.mysql) but I've no idea how to use it.