[RESOLVED] number formats (again!)

[obscuregirl]

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, brucevde 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?

[shakti5385]

Use val()

[obscuregirl]

Use val()

Thanks, but it won't let me use val() as it expects a string and the id is a number.

[shakti5385]

id is a number.

But here you are getting the decimal also

[obscuregirl]

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

[obscuregirl]

But here you are getting the decimal also

sorry, I don't understand what you mean?

[obscuregirl]

I got an answer on another from JaganEllis. They suggested using creating a formula field using the formula I was using elsewhere for the id e.g. ToText({fieldname},0,"") and grouping on that formula instead of the fieldname itself. This worked fine.

[shakti5385]

This is also working
Right click on the Number and set the decimal as zero

[obscuregirl]

This is also working
Right click on the Number and set the decimal as zero

That only works on the number itself, if the number is included in a formula field you can't do that.

[shakti5385]

Yes you are correct

[obscuregirl]

Yes you are correct

Yes I know. But my original post was to do with formatting the numbers that are within a formula field! Anyway it's sorted now so further discussion is fairly irrelevant.

FYI: using the ToText({fieldname},0,"") would not allow numbers to display in numerical order in the grouping tree e.g. 1020 would come before 345. To ameliorate this, use the following formula: right('0000000000' & ToText({fieldname},0,""),10) but this would mean that leading zeros would appear in front of the numbers, so spaces could be substituted for the zeros instead.