combo box display format

[Chris]

I am using Access 2007.

I have a combo box where the right-most column displayed is a percentage.

That percentage is formatted in the source table as number, double, percent,
decimal places=0. It comes to the combo box via a query to deselect certain
records.

Everywhere I view the field it appears as "50%" -- except the combo box. It
is displayed as "50.00%". How do I format it for combo box use yielding
"50%" without problems where it does work correctly? I use the column's
value in calculation in VBA code.

Can someone show me the way, please?

 

[Douglas J. Steele]

Try using the Format function in the query.

Rather than SELECT Field1, Field2 FROM MyTable ..., use SELECT Field1,
Format(Field2, "#%") FROM MyTable ...

 

[Chris]

Thanks, Doug.

That worked great for the combo box list display. However, now I get a type
mismatch when I am selecting from the list where the item has a percentage
(null or blank in the percentage field work fine).

When the user selects from the list, I pass this column to a control in the
form "=[cboCG1].[column](3)", and that goes on to calculate amounts. Column
three is the re-formatted percent field.

Is there an easy fix or should I accept the two decimal places?

Thanks again,

Chris

Try using the Format function in the query.

Rather than SELECT Field1, Field2 FROM MyTable ..., use SELECT Field1,
Format(Field2, "#%") FROM MyTable ...

 

[Chris]

Doug,

Solved the type mismatch by displaying both the formatted and unformatted
percent fields. I use the unformatted for calculation.

I tried setting the formatted to 0" length. That didn't work, so I made the
original one as the last column and called it for calcs.

Then I made the width to display sufficient to hide the unformatted column
in favor of the formatted one.

Seem to work fine now.

Thanks for your help.
Chris

Try using the Format function in the query.

Rather than SELECT Field1, Field2 FROM MyTable ..., use SELECT Field1,
Format(Field2, "#%") FROM MyTable ...

 

[Douglas J. Steele]

That's probably the best approach.

The problem is caused by the fact that the Format function actually converts
whatever it's formatting into a string. Another approach would have been to
use to use the CSng or CDbl function when assigning the formatted value to
the variable:

"=CSng([cboCG1].[column](3))",

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Solved the type mismatch by displaying both the formatted and unformatted
percent fields. I use the unformatted for calculation.

I tried setting the formatted to 0" length. That didn't work, so I made
the
original one as the last column and called it for calcs.

Then I made the width to display sufficient to hide the unformatted column
in favor of the formatted one.

Seem to work fine now.

Thanks for your help.
Chris

 

[Chris]

Thanks. I'll play with that sometime. Thanks for the followup and
continuing education!
--
Thanks for your help,
Chris

That's probably the best approach.

The problem is caused by the fact that the Format function actually converts
whatever it's formatting into a string. Another approach would have been to
use to use the CSng or CDbl function when assigning the formatted value to
the variable:

"=CSng([cboCG1].[column](3))",

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Solved the type mismatch by displaying both the formatted and unformatted
percent fields. I use the unformatted for calculation.

I tried setting the formatted to 0" length. That didn't work, so I made
the
original one as the last column and called it for calcs.

Then I made the width to display sufficient to hide the unformatted column
in favor of the formatted one.

Seem to work fine now.

Thanks for your help.
Chris

 

[talibm]

Hi Doug,

I've lost my percent formatting in my combo box. Is form is in access 2003
and I have access 2007 on my pc. Some how the percent formattig disappeared.
I tried the Format(Field2, "#%"), but I have one decimal place and I lose the
decimal place. 6.5 is "6.". When I tried Format(Field2, "#.#%") It picked up
the one decimal place but for the records that didn't have a number behind
the decimal the decimal place still showed up. How do I fix this? Thanks

Talib
--
tm

That's probably the best approach.

The problem is caused by the fact that the Format function actually converts
whatever it's formatting into a string. Another approach would have been to
use to use the CSng or CDbl function when assigning the formatted value to
the variable:

"=CSng([cboCG1].[column](3))",

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Solved the type mismatch by displaying both the formatted and unformatted
percent fields. I use the unformatted for calculation.

I tried setting the formatted to 0" length. That didn't work, so I made
the
original one as the last column and called it for calcs.

Then I made the width to display sufficient to hide the unformatted column
in favor of the formatted one.

Seem to work fine now.

Thanks for your help.
Chris

 

[talibm]

I found the answer to my question. FormatPercent works perfectly for my
needs. Thanks