Tutorial :How do I change a column's Format to Percent using a SQL in VBA?



Question:

I have a query in VBA that Adds a few columns to a table. How can I change the format of the PercentSuccess column to Percent using SQL in VBA?

Here is the code I'm using to add the columns.

strSql5 = " Alter Table analyzedCopy3 " & _        "Add Column PercentSuccess Number, Success Number, prem_addr1 TEXT(50) "    DoCmd.SetWarnings False     DoCmd.RunSQL strSql5     DoCmd.SetWarnings True  

I've tried to use Format() but I can't get it to work for changing what I need to change. It only seems to change the things like Number, Text and so on.


Solution:1

You cannot set the Format property using SQL but you can do it through additional VBA code. Also you should know that certain field properties do not actually exist until they are assigned a value of which the Format property is one of them. The code below first gets a reference to the field in question, creates a new Format property with the desired value, and then appends it to the fields definition. The Microsoft Access UI is misleading because it makes you think the property already exists.

  Dim db   As Database    Dim tdef As TableDef    Dim fdef As Field    Dim pdef As Property      Set db = CurrentDb()    Set tdef = db.TableDefs("analyzedCopy3")    Set fdef = tdef.Fields("PercentSuccess")      Set pdef = fdef.CreateProperty("Format", dbText, "Percent")    fdef.Properties.Append pdef  

Finally this code only works if you use the DAO objects; you cannot set this property using ADO.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »