Access query data type conversion

Question

I'm trying to create a table from an existing table. I want the new column to be a percentage column with data type "Number". The code below changes column data type to "Text". I tried to use Format(num,"Percent"), it does the same.

SELECT 
    Format([DB].[Number1]/DB.[Number2],"0.00%") AS [New Column Name]
INTO newTable
FROM [OldTable] as DB;

Answer 1

Remove the "%", it forces the result to include it, which can only be stored in text type field.

SELECT 

Format([DB].[Number1]/DB.[Number2],"0.00") AS [New Column Name]

INTO newTable

FROM [OldTable] as DB;

to get a percentage multiply the value by 100

SELECT 

Format(([DB].[Number1]/DB.[Number2])*100,"0.00") AS [New Column Name]

INTO newTable

FROM [OldTable] as DB;

Answer 2

Use the query to store the raw quotients and adjust the field's Format property afterward.

SELECT 
    ([DB].[Number1] / DB.[Number2]) AS [New Column Name]
INTO newTable
FROM [OldTable] as DB;

You can open the table in Design View and choose "Percent" for the field's Format. If you want to do the same thing with VBA, I tested this in Access 2010:

Dim db As DAO.Database
Set db = CurrentDb
With db.TableDefs("newTable").Fields("New Column Name")
    .Properties.Append .CreateProperty("Format", dbText, "Percent")
End With

Another way to approach this would be to create the table, set the field format, and then use an "append query" (instead of a "make table query") to load your data into the table.