Query truncates memo fields

[MarkRobinson]

On my append query, memo fields get truncated at 255 or so characters. Is there a way to avoid this?

Specifically:
Table A has a memo field "Notes" in it.
My Append Query send the record to table B, which also has a "Notes" memo field in it, but the text in "Notes" Table A is truncated when it gets to table B.

Thx,
Mark

 

[PHV]

Could you please post the SELECT instruction of your append query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkRobinson]

I'm taking a table with 1 line per item ordered into a table where there is one line per order. After looking elsewhere, I'm coming to the conclusion that Queries don't do memo fields. I expect I'll have to write a Do Loop and move it line by line. Ugh


INSERT INTO xcartnames ( OrderNum, [User], TotalOrder, Number2, Number3, Number4, Field13, ShipVia, Field15, ShipCost, Field17, Field18, Field19, Payment, Field21, Field22, Notes, Field24, Title1, FName1, LName1, Company1, Title2, FName2, LName2, Address2, Address2A, City, State, Country, Zip, Title3, FName3, LName3, Address3, Address3A, City3, State3, Country3, Zip3, Phone, Field48, Field49, Email )
SELECT XCartIn.OrderNum, XCartIn.User, XCartIn.TotalOrder, XCartIn.Number2, XCartIn.Number3, XCartIn.Number4, XCartIn.Field13, XCartIn.ShipVia, XCartIn.Field15, XCartIn.ShipCost, XCartIn.Field17, XCartIn.Field18, XCartIn.Field19, XCartIn.Payment, XCartIn.Field21, XCartIn.Field22, XCartIn.Notes, XCartIn.Field24, XCartIn.Title1, XCartIn.FName1, XCartIn.LName1, XCartIn.Company1, XCartIn.Title2, XCartIn.FName2, XCartIn.LName2, XCartIn.Address2, XCartIn.Address2A, XCartIn.City, XCartIn.State, XCartIn.Country, XCartIn.Zip, XCartIn.Title3, XCartIn.FName3, XCartIn.LName3, XCartIn.Address3, XCartIn.Address3A, XCartIn.City3, XCartIn.State3, XCartIn.Country3, XCartIn.Zip3, XCartIn.Phone, XCartIn.Field48, XCartIn.Field49, XCartIn.Email
FROM XCartIn
GROUP BY XCartIn.OrderNum, XCartIn.User, XCartIn.TotalOrder, XCartIn.Number2, XCartIn.Number3, XCartIn.Number4, XCartIn.Field13, XCartIn.ShipVia, XCartIn.Field15, XCartIn.ShipCost, XCartIn.Field17, XCartIn.Field18, XCartIn.Field19, XCartIn.Payment, XCartIn.Field21, XCartIn.Field22, XCartIn.Notes, XCartIn.Field24, XCartIn.Title1, XCartIn.FName1, XCartIn.LName1, XCartIn.Company1, XCartIn.Title2, XCartIn.FName2, XCartIn.LName2, XCartIn.Address2, XCartIn.Address2A, XCartIn.City, XCartIn.State, XCartIn.Country, XCartIn.Zip, XCartIn.Title3, XCartIn.FName3, XCartIn.LName3, XCartIn.Address3, XCartIn.Address3A, XCartIn.City3, XCartIn.State3, XCartIn.Country3, XCartIn.Zip3, XCartIn.Phone, XCartIn.Field48, XCartIn.Field49, XCartIn.Email;

 

[PHV]

The culprit is the GROUP BY clause.
Things that truncate a memo to 255 characters:
DISTINCT predicate
aggregate function
GROUPing
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MarkRobinson]

Thought I was being smart by splitting the field. "Cardholder's name:" is toward the end of the memo field. Still didn't work.

UPDATE XCartIn SET XCartIn.Notes1 = Right([notes],Len([notes])-InStr([notes],"Cardholder's name:")+1);

 

[PHV]

Anyway, why using a GROUP BY clause as you don't use any aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886