Controll Formatting

[Guest]

I have a small form with two combo boxes and one unbound text box:
Combo Boxes:
cboRegion --> Displays list of 4 possible regions (user selection is
parameter for query)
cboPeriod --> Displays 12 periods (Jan, Feb, Mar, .... Dec) Use selection
is parameter for query.

Tex Box:
txtRevenue --> Displays the result of the query based on the parameters
selected from the two combo boxes.

This all works perfect, but... there is always a but. I'm having a problem
having the value displayed on the txtRevenue box in a format that applies.
As you may have guessed, the value displayed on the txtRevenue box is a
calculated number field. My goal is to have it display in "Standard"
numerical form.
I've set the expression that calculates the revenue in the query to format
#,##0.00;(#,##0.00)[Red]
I've set the txtRevenue box to the same format.

My problem is that no matter what I do, the txtRevenue box always displays
the result from the query as unformatted numbers
e.g. If the query returns 75650.65, I've setup the formatting to have the
result display as 75,650.65 but no matter what I do it always displays as
75650.65. One thing that strikes me as odd is that if I click on the
txtRevenue box in Form View mode and place the cursor before the first digit
of the value displayed and then hit backspace... once I tab out of the form,
the value displays the way I want it to.

Any ideas?

Thanks
Sebastian

 

[tina]

are you populating the Textbox control using VBA? if so, suggest you try
adding the formatting in the code, as

Me!TextboxName = Format(value, "#,##0.00;(#,##0.00)[Red]")

if you're populating the Textbox control with a DLookup() expression in the
control's ControlSource property, then try enclosing the DLookup in the
Format() function, as

=Format(DLookup("FieldName","QueryName","Criteria
string"),"#,##0.00;(#,##0.00)[Red]")

the above would be all on one line, of course.

hth

 

[Guest]

Tina,

Thank you so much.
Me!TextboxName = Format(value, "#,##0.00;(#,##0.00)[Red]") did it for my
form. I can now move on to my next challenge.

Thanks again.

Sebastian

are you populating the Textbox control using VBA? if so, suggest you try
adding the formatting in the code, as

Me!TextboxName = Format(value, "#,##0.00;(#,##0.00)[Red]")

if you're populating the Textbox control with a DLookup() expression in the
control's ControlSource property, then try enclosing the DLookup in the
Format() function, as

=Format(DLookup("FieldName","QueryName","Criteria
string"),"#,##0.00;(#,##0.00)[Red]")

the above would be all on one line, of course.

hth

I have a small form with two combo boxes and one unbound text box:
Combo Boxes:
cboRegion --> Displays list of 4 possible regions (user selection is
parameter for query)
cboPeriod --> Displays 12 periods (Jan, Feb, Mar, .... Dec) Use selection
is parameter for query.

Tex Box:
txtRevenue --> Displays the result of the query based on the parameters
selected from the two combo boxes.

This all works perfect, but... there is always a but. I'm having a problem
having the value displayed on the txtRevenue box in a format that applies.
As you may have guessed, the value displayed on the txtRevenue box is a
calculated number field. My goal is to have it display in "Standard"
numerical form.
I've set the expression that calculates the revenue in the query to format
#,##0.00;(#,##0.00)[Red]
I've set the txtRevenue box to the same format.

My problem is that no matter what I do, the txtRevenue box always displays
the result from the query as unformatted numbers
e.g. If the query returns 75650.65, I've setup the formatting to have the
result display as 75,650.65 but no matter what I do it always displays as
75650.65. One thing that strikes me as odd is that if I click on the
txtRevenue box in Form View mode and place the cursor before the first digit
of the value displayed and then hit backspace... once I tab out of the form,
the value displays the way I want it to.

Any ideas?

Thanks
Sebastian

 

[tina]

you're welcome

Tina,

Thank you so much.
Me!TextboxName = Format(value, "#,##0.00;(#,##0.00)[Red]") did it for my
form. I can now move on to my next challenge.

Thanks again.

Sebastian

are you populating the Textbox control using VBA? if so, suggest you try
adding the formatting in the code, as

Me!TextboxName = Format(value, "#,##0.00;(#,##0.00)[Red]")

if you're populating the Textbox control with a DLookup() expression in the
control's ControlSource property, then try enclosing the DLookup in the
Format() function, as

=Format(DLookup("FieldName","QueryName","Criteria
string"),"#,##0.00;(#,##0.00)[Red]")

the above would be all on one line, of course.

hth

I have a small form with two combo boxes and one unbound text box:
Combo Boxes:
cboRegion --> Displays list of 4 possible regions (user selection is
parameter for query)
cboPeriod --> Displays 12 periods (Jan, Feb, Mar, .... Dec) Use selection
is parameter for query.

Tex Box:
txtRevenue --> Displays the result of the query based on the parameters
selected from the two combo boxes.

This all works perfect, but... there is always a but. I'm having a problem
having the value displayed on the txtRevenue box in a format that applies.
As you may have guessed, the value displayed on the txtRevenue box is a
calculated number field. My goal is to have it display in "Standard"
numerical form.
I've set the expression that calculates the revenue in the query to format
#,##0.00;(#,##0.00)[Red]
I've set the txtRevenue box to the same format.

My problem is that no matter what I do, the txtRevenue box always displays
the result from the query as unformatted numbers
e.g. If the query returns 75650.65, I've setup the formatting to have the
result display as 75,650.65 but no matter what I do it always displays as
75650.65. One thing that strikes me as odd is that if I click on the
txtRevenue box in Form View mode and place the cursor before the first digit
of the value displayed and then hit backspace... once I tab out of the form,
the value displays the way I want it to.

Any ideas?

Thanks
Sebastian