Formatpercent() as text problem 1

[schakarian]

Hi,

I am creating a make-table query in sql and when I place the following line,

IIf([Total Payments]<>0,formatpercent([Total Check Payments]/[Total Payments]),0) AS [Check Payment %]

the field that's created in the new table, Check Payment %, gets created as text field. I wan't it to display as a percent, but be a number field. (The reason for this is because it will be exported to excel where calculations may be done).

Any help is appreciated.

 

[PHV]

Simply don't use any Format function in the make-table query if you want numeric values.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Obviously your "FormatPercent" function applies some formatting (such as appending a % sign) to the computed quotient. To store a field with non-numeric characters both access and excel must use text fields.

Just compute the quotient and change the format to "%" after you have exported it to Excel.

In Access you can chose a format for the column that displays it as a % even though the underlying data type is numeric.

 

[schakarian]

For testing purposes, when I create a table in design mode and set the field to double with a percent format. This is exactly what I want, the long decimal underlying number (ie. 12.666666666), but displayed as a percent (12.67%).

But I'm trying to dynamically create this table with code.
What I'm trying to do is have the field in the newly created table be a number(double) field with a percent format. Is there any other function that I can use in my sql to specify the format as percent, but still keep the datatype a number?

 

[schakarian]

Ooops, I meant .12666666 above

 

[schakarian]

Sorry, I meant .12666666666 above

 

[Golom]

To do this in code you need to append a "Format" property to the properties collection of the field and set its value to "Percent". Something like

Dim tb As DAO.TableDef
Dim pp As DAO.Property
Dim fd As DAO.Field
Set tb = db.TableDefs("TableName")
Set fd = tb.Fields("FieldName")
Set pp = New DAO.Property
pp.Name = "Format"
pp.Type = "Text"
pp.Value = "Percent"
fd.Properties.Append pp

 

[schakarian]

I tried the above with some modification and can't get it to work...

Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property

Set db = CurrentDb()
Set tb = db.TableDefs("APA SR Final")
Set fd = tb.Fields("WE 1/9/2004 % Scanned")

then when I put:
fd.Properties("Name") = "Format"

all I get is that the field name [WE 1/9/2004 % Scanned] changes to [Format]

What I need is for the field to convert from Text to Number (Double) data type, and for the format to be Percent.

Help.

 

[JonFer]

You have to create the new property first and then append it to the properties collection for the field. Look at Golom's code or look it up in help for examples.

John

http://www.interautomateconsulting.com