469,312 Members | 2,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

How to set field format as percentage

759 512MB
Hello !
Please ! Can you teach me how to set a field format, in a table, as PERCENT ? Via VBA, of course

A FIELD in a TABLE.

Look:
I have a Make Table query. A field in this query has PERCENT format.
If I run the query, the field is shown OK, as PERCENT. But the field in the created table hasn't the same format.
Also I have a list box where it's row source is that table. And, as far as I know, I can not format ONLY ONE COLON in my list box to show PERCENT. So I need to change the field format in the table.

Any solution is OK if in the list box that field is shown as PERCENT.

Thank you again.
Jun 4 '11 #1

✓ answered by ADezii

The answer, Mihail, is by no means intuitive. I created a little Public Sub-Routine for you that will do the trick.
  1. Copy-N-Paste the following Sub-Routine to a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub FormatFieldToPercent(strTableName As String, strFieldName As String)
    2. Dim db As DAO.Database
    3. Dim tdf As DAO.TableDef
    4. Dim fld As DAO.Field
    5. Dim prop As DAO.Property
    6.  
    7. Set db = CurrentDb()
    8. Set tdf = db.TableDefs(strTableName)
    9. Set fld = tdf.Fields(strFieldName)
    10. Set prop = fld.CreateProperty("Format", dbText, "Percent")
    11.  
    12. fld.Properties.Append prop
    13. End Sub
  2. To change the Format of the Field to 'Percent', pass to the Sub-Routine the Name of your newly created Table along with the Field Name.
  3. Assuming your New Table is named tblPercent, and the Field that needs to be formatted to 'Percent' is [Percentage]:
    Expand|Select|Wrap|Line Numbers
    1. Call FormatFieldToPercent("tblPercent","Percentage")
  4. If you now Open tblPercent, you will see that the [Percentage] Field is now properly formatted to 'Percent' with the Data being displayed as Percentages. This change will also persist.
  5. Any further questions, feel free to ask.

8 12112
NeoPa
32,173 Expert Mod 16PB
Do you mean Field? Or really Control?
Jun 4 '11 #2
ADezii
8,800 Expert 8TB
The Formatting of the Field ('PERCENT') in the Original Table will not persist after the Make Table Query is executed. You can either Manually or Programmatically change the Formatting of this Field in the New Table after it has been created.
Jun 5 '11 #3
Mihail
759 512MB
Thank you for reply, ADezii.

THIS is my question: How to ? How to change the formatting of this field, PROGRAMMATICALLY (from VBA), AFTER the table has been created.

I don't know why, but my first post has been concatenated with my second post (the answer to NeoPa question). "A FIELD in a TABLE." was the first row from my second post.
Jun 5 '11 #4
ADezii
8,800 Expert 8TB
The answer, Mihail, is by no means intuitive. I created a little Public Sub-Routine for you that will do the trick.
  1. Copy-N-Paste the following Sub-Routine to a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub FormatFieldToPercent(strTableName As String, strFieldName As String)
    2. Dim db As DAO.Database
    3. Dim tdf As DAO.TableDef
    4. Dim fld As DAO.Field
    5. Dim prop As DAO.Property
    6.  
    7. Set db = CurrentDb()
    8. Set tdf = db.TableDefs(strTableName)
    9. Set fld = tdf.Fields(strFieldName)
    10. Set prop = fld.CreateProperty("Format", dbText, "Percent")
    11.  
    12. fld.Properties.Append prop
    13. End Sub
  2. To change the Format of the Field to 'Percent', pass to the Sub-Routine the Name of your newly created Table along with the Field Name.
  3. Assuming your New Table is named tblPercent, and the Field that needs to be formatted to 'Percent' is [Percentage]:
    Expand|Select|Wrap|Line Numbers
    1. Call FormatFieldToPercent("tblPercent","Percentage")
  4. If you now Open tblPercent, you will see that the [Percentage] Field is now properly formatted to 'Percent' with the Data being displayed as Percentages. This change will also persist.
  5. Any further questions, feel free to ask.
Jun 5 '11 #5
Mihail
759 512MB
Thank you very much, ADezii.

I am too tired now (is 23:15), but tomorrow, in the morning, I'll try to understand your code (for farther use :) ).

Thank you !!!!
Jun 5 '11 #6
ADezii
8,800 Expert 8TB
You are quite welcome, comprehending Code after a good night's sleep is always preferable.(LOL).
Jun 5 '11 #7
Mihail
759 512MB
THANK YOU , ADezii. The code work as I need.
But now :) I wish to learn more about .CreateProperty instruction. And Access help is poor.
Jun 6 '11 #8
NeoPa
32,173 Expert Mod 16PB
Creating properties in VBA is indeed quite advanced. Many properties shown in the Properties pane of Access objects are actually not set unless they are set to True. IE. To set such a property, it first needs to be added to the Properties collection (correctly). After it exists, it can be set to True or False whenever required. This means that when desiring to set such a property there must be code to set the value; code to create the property; and code to determine whether or not the property exists before deciding which code needs to be run (Trying to create a property that already exists triggers an error of course).

This is much more complicated than managing controls of course ;-)

An alternative approach is to use some SQL code that is simply called by the VBA, but that's another story (and even that involves more advanced SQL).
Jun 6 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Henry Stockbridge | last post: by
2 posts views Thread by Leon Shaw | last post: by
9 posts views Thread by David Rysdam | last post: by
3 posts views Thread by bobdydd | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.