Change Format in Access VBA (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 10:08
Joined
Dec 3, 2013
Messages
177
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:

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

Registered User.
Local time
Today, 10:08
Joined
Oct 7, 2009
Messages
78
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

Registered User.
Local time
Today, 10:08
Joined
Dec 3, 2013
Messages
177
How can I change the format in VBA?

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

Regards,
Ben
 

ino_mart

Registered User.
Local time
Today, 10:08
Joined
Oct 7, 2009
Messages
78
After the table is created, how is the field [AAA 2014 % TNS] defined? Is it double precission, is it a decimal, long integer?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:08
Joined
Feb 19, 2013
Messages
16,553
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
 

Users who are viewing this thread

Top Bottom