number formats (again!)

Hi

I'm having a problem with displaying my reports through my application at runtime. One of the formula fields on the report contains the id of the record I'm displaying and if the record's Verified field = True, it also shows a string. The formula is this:

If {?PubNoVer} = "Yes" then
if {View_TM_Get_BookID_BY_Surname.Verified} = True Then
{View_TM_Get_BookID_BY_Surname.BookID} & " Verified"
Else
{View_TM_Get_BookID_BY_Surname.BookID} & " "
Else
" "

In the report preview window, this all looks fine but running it through my application made any four digit id look wrong because it added a thousand separator and decimal places e.g. 1,512.00

In a previous post on another forum, a user very kindly showed me that using ToText on the ID field would get rid of the decimal places, so that's fine, but I still have the thousand separator and can't get rid of it!

Does anyone have any ideas?just format the field


right click on field, select format field, go to number that and select the format u want to display in|||just format the field


right click on field, select format field, go to number that and select the format u want to display in

Yeah, I did that but it only shows the correct format in the preview window, when running the report through the application, it shows the thousand separator and the decimal places even though i'd specified for that not to happen!

OK, I've found half an answer on another site. To get rid of both the decimal places and the thousands separators you use:

ToText({fieldname},0,"")

This works but because I'm also grouping on the id field, the ids still show with a thousands separator on the group tree at the side of the report! Anybody know how to format that?!|||You could create a formula like:

right('0000000000' & totext({fieldname}, 0, ""), 10)

and then group on that formula instead of the fieldname.
Trouble is you get a load of leading zeros in front of the number in the group tree.
You could limit this by finding the maximum length of the field with a whilereadingrecords formula and changing the formula to
right(replicatestring('0', {@.maxlen}) & totext({fieldname}, 0, ""), {@.maxlen})

You could use a space instead of a zero, but the numbers in the group tree won't line up, although they will still be in numeric order.

If anyone knows another way (apart from setting the PC's local settings) I'd like to know...|||You could create a formula like:

right('0000000000' & totext({fieldname}, 0, ""), 10)

and then group on that formula instead of the fieldname.
Trouble is you get a load of leading zeros in front of the number in the group tree.

Thanks JaganEllis. Instead of the formula you suggested, I used ToText({fieldname}, 0, "") and grouped on it and this worked perfectly!|||I can see how that helps with the format of the group tree data, but what about the group ordering? Unless all your number have the same number of digits, the order will be messed up (if important to you).
e.g. 300 will be after 2000 because the string '3' is > the string '2'|||I can see how that helps with the format of the group tree data, but what about the group ordering? Unless all your number have the same number of digits, the order will be messed up (if important to you).
e.g. 300 will be after 2000 because the string '3' is > the string '2'

Ah right... I see what you mean! I've used spaces instead of zeros. Thanks!