473,836 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to set field format as percentage

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


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
8 13659
32,584 Recognized Expert Moderator MVP
Do you mean Field? Or really Control?
Jun 4 '11 #2
8,834 Recognized Expert Expert
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 Programmaticall y change the Formatting of this Field in the New Table after it has been created.
Jun 5 '11 #3
759 Contributor
Thank you for reply, ADezii.

THIS is my question: How to ? How to change the formatting of this field, PROGRAMMATICALL Y (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
8,834 Recognized Expert Expert
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
    7. Set db = CurrentDb()
    8. Set tdf = db.TableDefs(strTableName)
    9. Set fld = tdf.Fields(strFieldName)
    10. Set prop = fld.CreateProperty("Format", dbText, "Percent")
    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
759 Contributor
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
8,834 Recognized Expert Expert
You are quite welcome, comprehending Code after a good night's sleep is always preferable.(LOL ).
Jun 5 '11 #7
759 Contributor
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
32,584 Recognized Expert Moderator MVP
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

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

Similar topics

by: Henry Stockbridge | last post by:
I am doing some database documentation, and need to convert the field type and field formats in my code into terms I can convey to others (i.e. Date/Short Date, Number/Double, and so forth.) Any help you can lend would be appreciated... Sub FieldInventory() Dim db As Database Dim tdf As TableDef
by: HardBap | last post by:
I have an Access database that has an input mask on a field, !\(999") "000\-0000;0;_ , that formats a phone number. When I fill a DataSet using an OldDbDataAdapter some of the records lose the formatting. For example: Sam Jones (999) 555-1212 *this is good* Joe Smith 9995551212 *this is bad* Why would some records lose the formatting? There is no ryme or reason (that I can see anyway) why some records keep the formatting and some
by: Leon Shaw | last post by:
What is the best way (or How) to format a form textbox field so that when the user is entering data the text don't move (like it's trying to adjust itself or something) around?
by: David Rysdam | last post by:
I have a large amount of data that I copy in and out of Sybase very often. Now I also want to copy this data in and out of postgres. I have an existing script that creates the entire database(s) from scratch in Sybase and then uses the Sybase bulk copy tool "bcp" to copy the data in. I already did a brute force port of this script to postgres once, but I'm trying to do it more elegantly now that I know what issues I'm going to run...
by: bobdydd | last post by:
Hi Everybody I am using VB.NET Express with SQL ServerExpress In VB6 and Access 2000 you could specify a number to display as a percentage to 2 decimal places. How can you do this with VB.NET Express with SQL ServerExpress. I have been into Properties>> Data>>DataBindings>>Advanced and put a
by: hunslair | last post by:
This is a really basic question. I am taking a teach yourself beginners course on Access 2003 and have run into a road block on one practice. In a select query, I have created a simple calculation field. Markup:(-)/ which is to formatted as Percent with 0 Decimal places. When I click into the Format box is am offered
by: alfamash | last post by:
I am working on a database created by someone else. The date field in the database accept dates like 01/01/2007 but stores 1/1/2007. Please how do I format this date field to accept 01/01/2007 and stores as 01/01/2007 in the database. Thank you Alfamash
by: ARC | last post by:
This is taken from the "What's New in Access 2007" page. However, I've looked through all the properties of a text field memo box, and cannot find the append only option. Does anyone know how to use this feature? ------------------ "Memo fields are useful for storing large amounts of information. With Office Access 2007, you can set the Append Only property to retain a history of all changes to a Memo field. You can then view a history...
by: Carlos | last post by:
Hi all, I just discovered that a field that contains an e-mail address in a gridview does not work as it used to. That is, I had this DataFormatString: <a href=mailto:{0}>{0}</a> This thing does not work anymore, I am not aware of what update was performed by the system administrators that took away that capability to display the e-mail in the column with an e-mail address format. Does anyone know what may be happening?
by: Chris Clavius | last post by:
I have exported data from access 2003 table to excel where my date field was not recognized as date. After looking closely to my table I then realized that I didn't set the field's format to any of the date type. I tried to import the table structure and changed the format to yyyy/mm/. I then appended the data to my new table but still didn't solve the problem. Please help me to solve this.
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.