NeoSoft Support Forum :: View topic

through

[^/[Print]\]
Goto page 1, 2 Next :| |:
NeoSoft Support Forum -> NeoBookDBPro

#: About SQL Statement Author: carlos torres, Location: Pamplona, Colombia

Posted: Fri Jun 20, 2008 6:41 am
—
Hi everybody

I'm trying to upgrade an old app developed with dbf to dbp.

When I use this statement with integer values it works fine

Code:

dbpExecSQL "DbList" "UPDATE Db SET Nota6=[#34][Num][#34]" ""

But when [Num] contains any decimal value (real number or single) it has a wierd behavior.

ie. [Num] is 1.6 it shows 1,60000002384186
ie. [Num] is 2.3 it shows 2,29999995231628
ie. [Num] is 3.5 it shows 3.5
ie. [Num] is 3.6 it shows 3,59999990463257
ie. [Num] is 3.7 it shows 3,70000004768372

I just need 1.6 or 3.7 values. Only one decimal on the mantissa.

How can I avoid this kind of results?
I's dbp bug?

I will appreciate any help, TIA

regards
carlost

#: Author: Wrangler, Location: USA

Posted: Fri Jun 20, 2008 8:24 am
—
I have a similar problem with a currency field in Access db:

../ Get total item sales per model
FileLen "[pubdir]products.dat" "[flen]"

Loop "1" "[flen]" "[x]"
FileRead "[pubdir]products.dat" "[x]" "[getproduct]"
dbpQuery "invoicer" "products" "proddesc = [#34][getproduct][#34]"
dbpSum "invoicer" "products" "subtotal" "[total[getproduct]]"
dbpSum "invoicer" "products" "qty" "[[getproduct]sales]"
Math "[totalitems]+[[getproduct]sales]" "" "[totalitems]"
endloop

Brings this result:

It only does it to a couple of them. All the rest display correctly. Don't know if this is related to Carlos problem or not.

#: Author: Neosoft Support, Location: Oregon, USA

Posted: Fri Jun 20, 2008 11:17 am
—
What you need to do is use DBPro's dbpSetFieldProperties action to define the DisplayFormat for the field. This will allow you to customize the number of decimal places, etc.

#: Author: Wrangler, Location: USA

Posted: Fri Jun 20, 2008 11:25 am
—
I'm using a currency field. Shouldn't it default to 2 decimal places?

#: Author: Neosoft Support, Location: Oregon, USA

Posted: Mon Jun 23, 2008 10:55 am
—
Wrangler, I think the problem with your numbers is caused by the Math action. In your example, you left the second parameter empty which causes the result to have as many or as few decimal places as it needs. Instead, if you want the result to have exactly two decimal places, you should put "2" in this parameter. For example:

Math "[totalitems]+[[getproduct]sales]" "2" "[totalitems]"

#: Author: Wrangler, Location: USA

Posted: Mon Jun 23, 2008 11:13 am
—
The math action totals the number of items, which isn't affected by this. However, I fixed it by adding a math action after the dbpsum:

../ Get total item sales per model
FileLen "[pubdir]products.dat" "[flen]"

Loop "1" "[flen]" "[x]"
FileRead "[pubdir]products.dat" "[x]" "[getproduct]"
dbpQuery "invoicer" "products" "proddesc = [#34][getproduct][#34]"
dbpSum "invoicer" "products" "subtotal" "[total[getproduct]]"
Math "[total[getproduct]]" "2" "[total[getproduct]]"
dbpSum "invoicer" "products" "qty" "[[getproduct]sales]"
Math "[totalitems]+[[getproduct]sales]" "2" "[totalitems]"

endloop

I guess I assumed because I set the field properties for the subtotal field to currency in startup actions:

dbpSetFieldProperties "invoicer" "products" "subtotal" "Alignment=Center;DisplayFormat=#.00"

it would also display as such after the dbpsum. Funny thing is, it did it for all of them but the two.

#: Author: Neosoft Support, Location: Oregon, USA

Posted: Mon Jun 23, 2008 11:24 am
—
dbpSum works outside the constraints of the table, but I suppose it could borrow the field's display format for its result. However, what if the display format includes something that isn't normally part of a number such as a dollar sign? That could mess up any subsequent math actions. For example:

DisplayFormat=$#.00

or

DisplayFormat=£#.00

#: Author: Wrangler, Location: USA

Posted: Mon Jun 23, 2008 11:30 am
—
You have a point there. If the $ is included in the field data, it wouldn't work. But it's not stored in there, is it? Can't see how it could sum it up if it was.

#: Author: domino, Location: Notts UK

Posted: Tue Jun 09, 2009 5:46 am
—
I`m having a similar issue with my application

In effect I`ve created a shopping cart and whether I use math or dbprosum I can`t get it to display .00 where a value has no decimal places.. (ie whole £ pounds no pence)

eg: it displays 24 rather than 24.00

(in math mode setting decimal places to either 2 or -1 has no effect.)

#: Author: dpayer,

Posted: Tue Jun 09, 2009 8:27 am
—

domino wrote:

I`m having a similar issue with my application

In effect I`ve created a shopping cart and whether I use math or dbprosum I can`t get it to display .00 where a value has no decimal places.. (ie whole £ pounds no pence)

eg: it displays 24 rather than 24.00

(in math mode setting decimal places to either 2 or -1 has no effect.)

At the point of making a display, it is no longer needed to be a number (specifically) it is a string. You can do a strsearch for the 'dot' and if it doesn't exist, add a .00 to the string before displaying.

David P

#: Author: domino, Location: Notts UK

Posted: Tue Jun 09, 2009 8:53 am
—

Quote:

Thanks David - I guess that's the pragmatic solution, and what I`ll have to do, but was rather hoping there was a setting lurking somewhere.

Cheers
Dave

#: Author: David de Argentina, Location: Buenos Aires, Argentina

Posted: Tue Jun 09, 2009 8:56 am
—
Or...

Use NeoFormatNumber Plugin in order to do a specific format.

You could check this plugin at:

http://www.neosoftware.com/neobook/modules/plugins/singlefile.php?cid=9&lid=54

Greetings from Buenos Aires,
David de Argentina

#: Author: Wrangler, Location: USA

Posted: Tue Jun 09, 2009 8:57 am
—
For me, the math method works if used right before display or after the sum:

Math "[total]" "2" "[total]"

#: Author: domino, Location: Notts UK

Posted: Tue Jun 09, 2009 3:26 pm
—
Thanks for the replies....

Unfortunately none of them quite deal with the main issue which appears to be the inability of a "currency field" to retain the .00 for whole numbers.

Any of your solutions are fine as workarounds - but mean that the dbpro sum command cannot be used 'as is' if the .00 is required.

For my application it's not a huge issue as I simply reformatted the relevant fields as strings and ran math loops thus enabling the offending .00 results to carry through to the preview, report and the completed orders table.

Cheers
Dave

#: Author: Neosoft Support, Location: Oregon, USA

Posted: Wed Jun 10, 2009 9:41 am
—
The following was added to DBPro 1.2 released in January:

Quote:

The value returned by the dbpSum action is now formatted using the calculated field's display format as specified in dbpSetFieldProperties.

So if you set the field's display format with dbpSetFieldProperties that format will be used for the results produced by dbpSum.

NeoSoft Support Forum -> NeoBookDBPro

All times are GMT - 7 Hours

Goto page 1, 2 Next :| |: Page 1 of 2