Fomrat Number on Datasheet

[Snowflake68]

I have a number field and that I want to display zeros on a datasheet form but also show the commas for the thousands. Eg. 2,500
I formatted the text box on the datasheet form to #,### but this doesnt show the zeros. How do I format it so that also show the zeros.

row 1 value = 2,500
row 2 value = 0

row 1 shows correctly but row 2 shows nothing

 

[pbaldy]

So it only shows 25? That would be really odd. If the value is 2500 and it's not formatting with the comma, my guess is the value is seen as text rather than a number. Either change the data type of the field or look for something along the way changing how it's seen (Format function, IIf maybe, etc).

 

[MajP]

(0) Digit placeholder. Display a digit or a zero. If the expression has a digit in the position where the 0 appears in the format string, display it; otherwise, display a zero in that position.If the number has fewer digits than there are zeros (on either side of the decimal) in the format expression, display leading or trailing zeros. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, round the number to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, display the extra digits without modification.

(#) Digit placeholder. Display a digit or nothing. If the expression has a digit in the position where the # appears in the format string, display it; otherwise, display nothing in that position. This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.

 

[CJ_London]

have your tried

#,###.00

 

[pbaldy]

Oh, were the decimals the problem? I went off in the wrong direction then.

 

[CJ_London]

maybe, maybe not, post could be interpreted in a number of ways.

 

[Snowflake68]

So it only shows 25? That would be really odd. If the value is 2500 and it's not formatting with the comma, my guess is the value is seen as text rather than a number. Either change the data type of the field or look for something along the way changing how it's seen (Format function, IIf maybe, etc).

no it does show 2,500 correctly, the issue is that I still want to display single 0 values too but when the value is just 0 it doesnt display anything just looks like an empty value.

 

[Snowflake68]

I've sorted it now just formatted it to #,##0 and this works perfectly.

Thanks everyone for trying to help

 

[pbaldy]

Is the value 0 or is there nothing there? You can try this

#,##0

 

[Snowflake68]

Is the value 0 or is there nothing there? You can try this

#,##0

Thank you, I got there in the end

 

[CJ_London]

so we all had a different interpretation, all of them wrong:banghead: goes to show the benefit of providing before and after examples as per the modified first post

 

[pbaldy]

Ah yes, the edited post is clearer, and I was certainly on the wrong track. Glad you got it working.

 

[Snowflake68]

Ah yes, the edited post is clearer, and I was certainly on the wrong track. Glad you got it working.

Yes apologies for not providing a clear enough example from the outset, hence edited post.