Numerical Formatting

[Thompyt]

How do you get a number to show 11.10. Normal formatting will put it at 11.1 I kbow I can format(field,"00.00) and get it, but I do not want to have the beginning set 11 to be represented by 11.00. I am using a concantenated field for this. CRNumbers: [CR_NO] + ([Sub_No]*.01). This gets me Vers A. I tried Format([CR_NO]) + ([Sub_No]*.01), "Fixed") and Format([CR_NO]) + ([Sub_No]*.01), "0.00"). This gets me Vers B. I am trying to get Vers C.


I can get this: Vers A
11
11.01
11.02....


11.09
11.1

or this: Vers B
11.00
11.01
11.02....
11.09
11.10

I would like to have this
11
11.01
11.02....
11.09
11.10

Thanks

[drexasaurus]

I don't think there is a clean answer. My first few ideas:
Replace(CStr(Format([Number], "0.00")), ".00", "") --> That would be turning into a string and replacing all the unwanted .00s. You may not need to explicitly use CStr().
Also, you could create a helper function yourself if you didn't want to do something like up there if it's very frequently used, but in vba that really slows things down when custom functions are used in queries.

[June7]

These are not actually numeric data but really identifiers, like for chapter/verse? Only the two levels? These are text type fields?

So if Sub_No is null, no decimal part?

Probably several ways to accomplish. The Replace() calc suggested by drex is one. The CStr() is not needed because Format results in a string.