Change Format in Access VBA

[Ben_Entrew]

Hi all,

I got a simple problem here.

How can I change a number column in a three digit percent one?

Tried the following, however it just deletes the content of the column:

Public Sub TEST()
Dim a11 As Double
Dim year As Integer

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE DEPT_FILES SET [AAA 2014 % TNS] = FormatPercent([AAA 2013 % TNS],3)"

DoCmd.SetWarnings True

Does anyone have a clue?
Thanks in advance.

Regards,
Ben

 

[ino_mart]

I would say you have to go to the table design mode and change the "decimal places"-parameter of the related field to 3.

 

[Ben_Entrew]

How can I change the format in VBA?

The source table is created new each time I start the program.

Regards,
Ben

 

[ino_mart]

After the table is created, how is the field [AAA 2014 % TNS] defined? Is it double precission, is it a decimal, long integer?

 

[Ben_Entrew]

Hi ino_mart, it is defined as double.

 

[GinaWhipp]

Hmm, sounds like you need an ALTER TABLE statement, have a look at...

http://office.microsoft.com/en-us/access-help/alter-table-statement-HP001032207.aspx

OR

http://office.microsoft.com/en-us/a...sing-a-data-definition-query-HA010341600.aspx

 

[CJ_London]

Gina is correct.

Just so you understand, the reason your sql code failed is that when using the formatpercent (or format for that matter) function it returns a text value - which cannot be updated into a numeric field

If you look at this link

http://msdn.microsoft.com/en-us/library/8zh1kxd7(v=vs.90).aspx

you will see it is described as

function formatpercent(....) as string