SELECT INTO define columns datatype

[thomasamillergoogle]

SELECT [Main(DPI)].CaseNumber, [Main(DPI)].Title AS CaseTitle, ' ' AS
Recommendations, ' ' AS Conclusions , ' ' AS RelatedToAllegation, ' '
AS RelatedToManagementControls, ' ' AS ReviewCloseDate, ' ' AS
CaseComments1

INTO [Legacy]
FROM [SUBJ/REC(DPI)] RIGHT JOIN [Main(DPI)] ON
[SUBJ/REC(DPI)].SubjectID = [Main(DPI)].SubjectID
WITH OWNERACCESS OPTION;



When this Access SQL statement is executed the resulting table [Legacy]
has a datatype of TEXT for the CaseComments1 column. I would like to
define it explicitly as MEMO. How can this be accomplished?

 

[Dirk Goldgar]

SELECT [Main(DPI)].CaseNumber, [Main(DPI)].Title AS CaseTitle, ' ' AS
Recommendations, ' ' AS Conclusions , ' ' AS RelatedToAllegation, ' '
AS RelatedToManagementControls, ' ' AS ReviewCloseDate, ' ' AS
CaseComments1

INTO [Legacy]
FROM [SUBJ/REC(DPI)] RIGHT JOIN [Main(DPI)] ON
[SUBJ/REC(DPI)].SubjectID = [Main(DPI)].SubjectID
WITH OWNERACCESS OPTION;



When this Access SQL statement is executed the resulting table
[Legacy] has a datatype of TEXT for the CaseComments1 column. I would
like to define it explicitly as MEMO. How can this be accomplished?

You can't do it with a SELECT INTO statement. I'd recommend you use two
SQL statements in succession: first a CREATE TABLE statement, which
will let you define the data type of every field in the output table,
and then an INSERT statement to select records and append them to the
new table.