Re: Using GROUP BY in query expression on MEMO fields it truncates data in MEMO fields to first 255 characters

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 11:53:48 -0600

Access is telling you that you can't have more than one "Value". You can
have many "First" as Row Headings. 

-- 
Duane Hookom
MS Access MVP
"[SolarAngel]" <not-for-mail> wrote in message
news:uDc46mH3EHA.1396@tk2msftngp13.phx.gbl...
> I would do something like that, but unfortunately I already have TRANSFORM
and PIVOT with First()
>  (Access tell me that I can't use more than one First for Value),
> I have INNER JOINS, LEFT JOIN, ... 7 tables linked with one that is
rotated (simple query) q=).
>
> yes, custom build (VB immediate print out):
>
> TRANSFORM First(Company_Custom_Field_Data.Value) AS FirstOfValue SELECT
Company.Code_Company, Company_Address.Code_Company_Address,
> Company_Address.Company_Name AS Address_Company_Name,
Company.Company_Name, Company.Complement_Name,
> Company_Address_Type.Company_Address_Type_Name, Company_Address.Address_1,
Company_Address.Address_2, Company_Address.Address_3,
> Company_Address.Zip_Code, Company_Address.Town, Company_Address.Country,
Company_Address.Tel, Company_Address.Fax,
> Company_Status.Company_Status_Name AS Company_Status, Company.Site_Web,
Company.Comments FROM Company_Status RIGHT JOIN
> (Company_Address_Type RIGHT JOIN ((Company LEFT JOIN (Tables_Reference
RIGHT JOIN Company_Custom_Field_Data ON
> Tables_Reference.Code_table = Company_Custom_Field_Data.Code_Table) ON
Company.Code_company =
> Company_Custom_Field_Data.Code_Company) LEFT JOIN Company_Address ON
Company.Code_company = Company_Address.Code_company) ON
> Company_Address_Type.Code_Company_Address_Type =
Company_Address.Code_Company_Address_Type) ON
Company_Status.Code_Company_Status =
> Company.Company_Status WHERE Company.Code_Company = 3550 GROUP BY
Company.Code_Company, Company_Address.Code_Company_Address,
> Company_Address.Company_Name , Company.Company_Name,
Company.Complement_Name, Company_Address_Type.Company_Address_Type_Name,
> Company_Address.Address_1, Company_Address.Address_2,
Company_Address.Address_3, Company_Address.Zip_Code, Company_Address.Town,
> Company_Address.Country, Company_Address.Tel, Company_Address.Fax,
Company_Status.Company_Status_Name , Company.Site_Web,
> Company.Comments PIVOT Tables_Reference.Code_table
>
> -OR OUTPUT FOR MORE COMLEX BUILD FOR OFFERS-
>
> TRANSFORM First(Offer_Custom_Field_Data.Value) AS FirstOfValue SELECT
Offer.Code_Offer, Offer.Code_Interlocutor,
> Offer.Code_Product_Type, Offer.Date_Offer, Trim(Interlocutor.Civility + '
' + Interlocutor.First_Name + ' ' +
> Interlocutor.Last_Name) AS Interlocutor, Users.Full_Name AS Responsible,
Offer.Date_Ratification, Offer.Probability,
> Offer_Status.Offer_Status_Name, Offer.Comment, Offer.Offer_Price,
Offer.Offer_Price_WTax, Products.Product_Name,
> Offer_Products.Price, Offer_Products.Quantity, ((Offer_Products.Tax *
0.01) * Offer_Products.Price) AS Price_WTax,
> Offer_Products.Delivered, Offer_Products.Delivered_Date,
Offer_Products.Comments FROM Interlocutor RIGHT JOIN (Products RIGHT JOIN
> (Users RIGHT JOIN (Offer_Status RIGHT JOIN (Offer_Products LEFT JOIN
(Tables_Reference RIGHT JOIN (Offer_Custom_Field_Data RIGHT
> JOIN Offer ON Offer_Custom_Field_Data.Code_Offer = Offer.Code_Offer) ON
Tables_Reference.Code_table =
> Offer_Custom_Field_Data.Code_Table) ON Offer_Products.Code_Offer =
Offer.Code_Offer) ON Offer_Status.Code_Offer_Status =
> Offer.Code_Offer_Status) ON Users.Code_User = Offer.Responsible) ON
Products.Code_Product = Offer_Products.Code_Product) ON
> Interlocutor.Code_interlocutor = Offer.Code_Interlocutor WHERE
Offer.Code_Company = 3550 GROUP BY Offer.Code_Offer,
> Offer.Code_Interlocutor, Offer.Code_Product_Type, Offer.Date_Offer,
Trim(Interlocutor.Civility + ' ' + Interlocutor.First_Name + ' '
> + Interlocutor.Last_Name) , Users.Full_Name , Offer.Date_Ratification,
Offer.Probability, Offer_Status.Offer_Status_Name,
> Offer.Comment, Offer.Offer_Price, Offer.Offer_Price_WTax,
Products.Product_Name, Offer_Products.Price, Offer_Products.Quantity,
> ((Offer_Products.Tax * 0.01) * Offer_Products.Price),
Offer_Products.Delivered, Offer_Products.Delivered_Date,
> Offer_Products.Comments PIVOT Tables_Reference.Code_Table
>
> And field where I have this problem is in first Company.Comments, and
second sample Offer.Comment, Offer_Product.Comments, ...(more
> in other custom build queries)
>
> as I have figured out you are telling me to set First(Offer.Comments) AS
Comments will return me full value of MEMO field?
>
> u jeeee it works q=0, only now I have to detect which field is it, ...
> I must see how this looks like in Design.
> ... this can not be applied in Design you must manually change SQL, this
probably why I couldn't make it right in Design,
> I was experimenting by Access always returns me error, you must this, you
must that, you may not, ...
>
> Thanks man, you have saved me a 4 hours at least
>
> q=)
>
> [SolarAngel]
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:OUZxX0G3EHA.1144@TK2MSFTNGP09.phx.gbl...
> | As Doug explained, this behavior is by design.
> |
> | If Access did really have to perform string comparison operations based
on
> | thousands or even millions of characters, the queries would be
completely
> | unworkable.
> |
> | Work around the issue by not asking Access to do such as thing. For
example,
> | if you want the full memo in a GROUP BY query, then use First() on the
memo
> | field so that Access can just return the whole thing instead of needing
to
> | group by the field.
> |
> | --
> | Allen Browne - Microsoft MVP.  Perth, Western Australia.
> | Tips for Access users - http://allenbrowne.com/tips.html
> | Reply to group, rather than allenbrowne at mvps dot org.
> |
> | "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> | news:uM%230f%23E3EHA.1292@TK2MSFTNGP10.phx.gbl...
> | > That's the way it has to work, unfortunately.
> | >
> | > Whenever you need to sort on a Memo field, truncation takes place.
That
> | > means using a Memo field in conjunction with ORDER BY, UNION (as
opposed
> | > to
> | > UNION ALL) or GROUP BY will cause truncation.
> | >
> | > --
> | > Doug Steele, Microsoft Access MVP
> | > http://I.Am/DougSteele
> | > (No private e-mails, please)
> | >
> | >
> | > "[SolarAngel]" <not-for-mail> wrote in message
> | > news:upsyQlE3EHA.1404@TK2MSFTNGP11.phx.gbl...
> | >> BUG: Using GROUP BY in query expression on MEMO fields it truncates
data
> | > in MEMO fields to first 255 characters
> | >>
> | >>
> | >> I have tested this in new database with 1 key column, and 2 memo
fields
> | > one containing less than 255 chars, and 1 with more than 255
> | >> chars in it.
> | >> Created two queries one not using GROUP BY and one using it (I called
> | >> this
> | > one ...ERROR...)
> | >>
> | >> I have attached this database as example of bug.
> | >>
> | >> Info: bug discovered this using ADO not sure what ver. (MDAC 2.7)
> | >>          also Access Ver. 2000 [9.0.3821 SR-1]
> | >>
> | >> who needs to know, also I will test this bug on Access in Office XP
> | >> latter
> | > this day.
> | >>
> | >> Is this a Bug or a limitation I am not sure,
> | >> there is no comment on this in MSDN so presume that this is a bug.
> | >>
> | >> what so ever it makes my life more difficult, like already it isn't
q=).
> | >>
> | >> [SolarAngel]
> |
> |
>
>

Relevant Pages