Formatting Unbound Text Box with Value from ComboBox

Question

Novice / Intermediate Access User.

I have a form in MS Access (O365 ProPlus) which has a ComboBox based on six column query.

AfterUpdate, I want to pull the Value from column of the ComboBox and populate it in an unbound text box. However, the formatting needs to be Currency and it's not carrying over correctly from setting the Currency formatting in the Form Properties Field for the Textbox.

I have tried formatting in the Control Source property when pulling the ComboBox column values that way which had limited success. It displayed the Currency format but when I try to use the value in the textbox later to be used in a calculated (Sum) total value field elsewhere on the form, it is only recognized as text.

The TotalValue Textbox only concatenates the textbox(es) with currency dollar signs.

Combobox1_AfterUpdate: ()
Textbox1 = Combobox1.Column (2)
Textbox2 = Combobox1.Column (3)
Textbox3 = Combobox1.Column (4)
TotalValueTextbox = Textbox1 + Textbox2 + Textbox3

Expected results are one number adding up numerical values of the aforementioned text boxes. Instead I get number1number2number3 as if I am concatenating text strings.

Currency format (if it works in preceding text boxes) comes with the dollar sign but decimals don't carry and it concatenates the values in lieu of adding them.

I even tried adding a tertiary test textbox and made its Control Source value equal to 12 times (arbitrary value) of one of the Textboxes and it multiplied correctly, but when multiple text boxes are involved using sum calculation as listed above it didn't work.

Answer 1

Using Format() function with Currency parameter results in a string with a $ sign character. Plus (+) character is a concatenation operator left over from old BASIC as well as arithmetic operator. Concatenation will have priority in some situations, as you encountered with textboxes on form.

Instead of formatting data on form, put $ in a label next to textbox.

Otherwise, calculate with direct reference to combobox columns.

= Combobox1.Column(2) + Combobox1.Column(3) + Combobox1.Column(4)

Answer 2

Problem is, that a combobox always returns text. If you feed it with numbers or currency, these will formatted using your default settings.

So, add some extra columns to hold your amounts with no currency formatting but forced to text using Str. In your source query of the combobox, add three columns:

Column5: Str([Amount1]
Column6: Str([Amount2]
Column7: Str([Amount3]

Then use convert the values to numbers with Val:

TotalValueTextbox = Val(Combobox1.Column(5)) + Val(Combobox1.Column(6)) +Val(Combobox1.Column(7))

Finally, specify the Format property of TotalValueTextbox as Currency and, in Combobox1, set the column width of the extra columns to 0 (zero).