How to define the type (int) for a new field in SQL SELECT INTO statement in MS Access

ms-access, select-into, sql

Solution

The reason it ends up as a binary is because the field in the existing table most likely is a binary field.

You could try doing something like this:

SELECT ExistingField1, ExistingField2, CAST(NewField AS int)
INTO NewTable
FROM ExistingTable

`CAST` does not work in MsAccess. However, this should work:

SELECT ExistingField1, ExistingField2, cInt(Field1 + Field2) AS NewField
INTO NewTable
FROM ExistingTable

Problem

I want to define the new field in a select into statement as integer. However, the NewField ends up as binary. How can I accomplish this? ``` SELECT ExistingField1, ExistingField2, NewField INTO NewTable FROM ExistingTable ``` I searched a lot but couldn't find a solution yet. Edit 1: I am performing the SELECT INTO statement from within the Microsoft Access application itself. (it is not a table in Access that points to a SQL Server table) Edit 2: NewField is created with the SELECT INTO statement. It does not exist in a pre-existing table.

Original source