How to set field format as percentage

[Mihail]

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.

[NeoPa]

Do you mean Field? Or really Control?

[ADezii]

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.

[Mihail]

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.

[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:
   

   Public Sub FormatFieldToPercent(strTableName As String, strFieldName As String)
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim prop As DAO.Property
    
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)
   Set fld = tdf.Fields(strFieldName)
   Set prop = fld.CreateProperty("Format", dbText, "Percent")
    
   fld.Properties.Append prop
   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]:
   

   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.

[Mihail]

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

[ADezii]

You are quite welcome, comprehending Code after a good night's sleep is always preferable.(LOL).

[Mihail]

THANK YOU , ADezii. The code work as I need.
But now :) I wish to learn more about .CreateProperty instruction. And Access help is poor.

[NeoPa]

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