Setting the format of a number field to percent 1

[jgill264]

Any assistance would be TRULY appreciated!

In Access, I am creating a new field in vba that is a double-precision data type, but have yet to find some good code that shows how to set the field format to PERCENT (preferably 2 decimals) - after the field has been created. Any suggestions?

 

[THWatson]

jgill264
An example...

Me.txtThree = Format(Me.txtOne/Me.txtTwo,"percent")

Tom

 

[jgill264]

Thanks, Tom! It didn't work, I think because my code is probably a little (a lot) rudimentary. I'm running the update in an execute query. I included the FORMAT portion in the only manner that it would be accepted by the system and it ran, but the field did not populate at all (see below).

I was thinking I might need to set the field format property to percent - or maybe using a TableDef, but I don't know a lot about that stuff. I could not find any code examples for updating the format property for a number in MS help.

I attached the sub below. The AddField function is a homemade function that I borrowed from someone else.

Again, thanks for looking at it!

------------------------------

Sub RunMktShare()

'This sub calculate markets share based on TotalBots.

Dim db As Database
Set db = CurrentDb

Call AddField("zBrandNameTotals", "BotMktShare", "DOUBLE", 0)

db.Execute "UPDATE zBrandNameTotals A INNER JOIN zProdClassTotals B ON A.ProdClass = B.ProdClass SET A.BotMktShare = FORMAT(A.SumTotalBot/B.SumTotalBot1, 'Percent')"

End Sub

 

[THWatson]

jgill264
I notice that you have 'percent' (single quotes) whereas it should be "percent" (double quotes). That may just be a typo, but thought I should point it out.

You could try setting the field Format property to Percent and see if that does it.

Tom

 

[jgill264]

Thanks - I get a compile error when I use double quotes.

How would I go about setting the field Format perperty to percent?

 

[THWatson]

jgill264
Actually, I was thinking that you would set the field property in the table...but since you are just creating this field, that counts that out.

However, what happens if you do the following...
1. Change Call AddField("zBrandNameTotals", "BotMktShare", "DOUBLE", 0)
to
Call AddField("zBrandNameTotals", "BotMktShare", "PERCENT", 2)

2. Remove the FORMAT and 'percent' from
FORMAT(A.SumTotalBot/B.SumTotalBot1, 'Percent')"

Tom

 

[jgill264]

The AddField function I'm using uses Select Case for each data type. Percent is not one of the data types in the function. I tried it anyway and it errored out at the function.

 

[THWatson]

jgill264
Have you looked in VBA Help at the CreateField method?

Tom

 

[RoyVidar]

I think you'll need to work with the properties of the field - and if it doesn't exist, which is likely when creating a new field, you'd probably need to create the property.

Just a typed up thingie, you'll need to test, add some more/better errorhandling ...

[tt]dim td as dao.tabledef
dim p as dao.property
dim fl as dao.field
dim db as dao.database

set db = currentdb
set td = db.tabledefs("yourtable")
set fl = td.fields("yourfield")
on error resume next
fl.properties("Format") = "Percent"
if err.number = 3270 then
err.clear
on error goto 0 ' or rather your errorhandler
set p = fl.createproperty("Format", dbText, "Percent")
fl.properties.append p
end if
on error resume next
fl.properties("DecimalPlaces") = "2"
if err.number = 3270 then
err.clear
on error goto 0 ' or rather your errorhandler
set p = fl.createproperty("DecimalPlaces", dbText, "2")
fl.properties.append p
end if[/tt]

Roy-Vidar

 

[jgill264]

HALLELUIAH!!! It worked like a champ!

Tom and Roy, Thanks so much for your assistance! This Tek-Tips site is a great thing and your experience and expertise are GREATLY appreciated by those of us who are code-challenged!

Thanks again!

John