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

From: [SolarAngel] (not-for-mail)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 19:29:56 +0100

I see that now

Thanks again

[SolarAngel]

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message news:ev2J1WI3EHA.1396@tk2msftngp13.phx.gbl...
| 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]
| > |
| > |
| >
| >
|
|