change data type of a query field

[ossse]

I have manually created a query. One of the field concatenates two table-fields, and looks as follows;

PM Basis: [riskRankingBasisMitigated] & " - " & [taskDetails]

I would like to make this field a memo field. Both 'riskRankingBasisMitigated' and 'taskDetail' are memo fields.

Does anybody know how to do this?

Thanks in advance

 

[Melagan]

I'm not quite sure I understand the question - or maybe the reason for the question. What are you wanting to do with PM Basis? Is this a text box on a form or a field on a report?

~Melagan
______
"It's never too late to become what you might have been.

 

[ossse]

PM Basis is a field in a Query. I am going to send the Query to excel. However excel shortens that field, but not others. I am assuming that it is shortening the field, because it is considered a 'Text' field. Other fields that are NOT shortened are directly from the tables (not concatenated).

Is that clearer? (it barely makes sense to me)

 

[Melagan]

Yes that is clear. Queries do not have field definitions like tables do, though. What method are you using to send your query to Excel? Are you doing it programatically or manually?

~Melagan
______
"It's never too late to become what you might have been.

 

[ossse]

I am/need to do it programatically, I am using TransferSpreadsheet (outputTo command doesn't work... it opens it in Excel 95/5.0 which only allows 255 characters). Any ideas?

An idea that I have (not sure if possible) is programatically;
- create a Table that is a Copy of the Query
- change the PM Basis field in the Table to a Memo type
- repopulate the PM Basis field (I know how to do this)
- then send it to excel using TransferSpreadsheet (I know how to do this)

Is this possible? If so, can you give me a hint on how to do the first two points.?

OR do you have any other ideas?

THANKS

 

[ossse]

I figured it out.

To create a table that is a copy of the Query:

strSQL="SELECT * INTO NewTable FROM tblTable"
db.Execute strSQL


To change a field data type:

ALTER TABLE tblTable ALTER COLUMN ThisMemo TEXT (255)


Thank you though!!!

 

[dhookom]

Have you checked your query to see if all characters are currently displayed? If not, you might be truncating the expression in the query with phrases/words like "DISTINCT", "DISTINCTROW", "GROUP BY",...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

I thought Remou figgured it out in thread705-1403455