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

Question

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.

Answer 1

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

Answer 2

For Access I would use this, then the parameter dialog does not show.

SELECT ExistingField1, cInt(0) AS NewField
into NewTable1
FROM Table1

For SQL Server

CAST(null AS int) AS NewField