combo box display format

G

Guest

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?
 
D

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 ...
 
G

Guest

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


Douglas J. Steele said:
Try using the Format function in the query.

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

Guest

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


Douglas J. Steele said:
Try using the Format function in the query.

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

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!)


Chris said:
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
 
G

Guest

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


Douglas J. Steele said:
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!)


Chris said:
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
 
G

Guest

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


Douglas J. Steele said:
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!)


Chris said:
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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top