ComboBox Columns & Blanks

[Curious27]

A ComboBox with five columns, three of them are numeric in a Table. The ComboBox shows the Columns as numeric (1,200.00), but when the TextBox shows the value, it drops the coma (1200) and the decimal. Do care about the decimal because in the table the numbers are formatted as a numeric whole numbers.

The problem I'm having is trying to get the coma to appear when it should (1,200) it does appear in the column. I have tried to format the values in the Combobox Row Source Select statement like this,

Format(tAmmo.GrossQty, "#,###") AS ["GrossQty"], Format([tAmmo].[TargetLevel], "#,###") AS ["TargetLevel"]

and it does add the coma, but then it creates another problem and that is when a zero value should show the textbox is blank and the my calculation get all missed up because it's blank. It even changes the zero value to a blank when looking at columns on the ComboBox.

Everything works like it should until a textbox gets a blank instead of a zero. I've spent two days on this and now I need some help.

How do I force a zero when the column forces a blank, all this because I placed a numeric format in the select statement?

[NeoPa]

Hi Curious.

Your format strings ("#,###") all say "Give me a string with commas where necessary (>3 digits) but don't show columns unless they, or something to the left, is >0. Oh, and by the way, don't show decimals either.".

If you want zero values to show as 0 then use "#,##0", and if you also want two decimal places to be included regardless then use "#,##0.00".

[Curious27]

Hi, how are you?

I was so close and yet so far way, all because of a zero.
Makes sense though, how can you plea for a zero if you don't ask for one.

Thanks again, my friend.

[NeoPa]

Hi Curious.

I'm good thanks :-)

It looks like you're learning as you go, which is progress. All progress is good. One tip I would give you, is to see if the Help page can tell you what you need to know first. It's what I use, even now, in many scenarios.

Sometimes it's hard even to know which one to look at. In this case you probably didn't even appreciate it was a number formatting problem until later, but once you do then a look at the Help page often helps solidify the understanding - as well as often giving extra information about something you might never otherwise have guessed.