CODEWITHSUNDEEP
ms-access
willem
willem

Reputation: 2717

How to convert access memo field to text field in access query?

I found this:

SELECT [PartsInfoRaw].[Types] as memofield, Replace([PartsInfoRaw].[Types],"","") AS textfield
into newTable FROM [PartsInfoRaw];

where Types is a memofield in PartsInfoRaw containing some description. The replace, for some reason, converts the memofield in a textfield. But it feels hacky, and I am not sure if it can turn sour. Does anyone have a better idea?

Upvotes: 0

Views: 19341

Answers (2)

David-W-Fenton
David-W-Fenton

Reputation: 23067

It's not clear to me what you're trying to accomplish but have you tried Left([MemoField], 255) in your SELECT?

Upvotes: 4

JeffO
JeffO

Reputation: 8043

Have you tested this on a [Types] record where the replace function will return more than 255 characters? You will find some of your [textfield] data chopped off.

The Replace function returns a string, so Access is assuming a text type field is appropriate.

I don't know why you have to use a select into other than it is easier than creating a table, deleting all the records and then appending the new data.

Try creating the result table you want first, so you can make sure both fields are type memo. Then turn your query into an Append (Insert Into) query. You will have to run a Delete query first if you only want the results of this query in the newTable table.

delete from newTable;

Your query as Append:

Insert into newTable(memofield, textfield)
SELECT [PartsInfoRaw].[Types] as memofield
     , Replace([PartsInfoRaw].[Types],"","") AS textfield
FROM [PartsInfoRaw];

Upvotes: 0

Related Questions