A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2004, 11:47 AM
[SolarAngel]
external usenet poster
 
Posts: n/a
Default BUG: Using GROUP BY in query expression on MEMO fields it truncates data in MEMO fields to first 255 characters

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]





Attached Files
File Type: zip GROUPBY_MEMO.zip (10.4 KB, 43 views)
  #2  
Old December 7th, 2004, 12:26 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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
...
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]






  #3  
Old December 7th, 2004, 03:57 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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
...
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]



  #4  
Old December 7th, 2004, 05:34 PM
[SolarAngel]
external usenet poster
 
Posts: n/a
Default

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" wrote in message ...
| 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" wrote in message
| ...
| 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
| ...
| 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]
|
|


  #5  
Old December 7th, 2004, 06:53 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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
...
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" wrote in message

...
| 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" wrote in message
| ...
| 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
| ...
| 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]
|
|




  #6  
Old December 7th, 2004, 07:29 PM
[SolarAngel]
external usenet poster
 
Posts: n/a
Default

I see that now

Thanks again

[SolarAngel]

"Duane Hookom" wrote in message ...
| 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
| ...
| 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" wrote in message
| ...
| | 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" wrote in message
| | ...
| | 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
| | ...
| | 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]
| |
| |
|
|
|
|


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Using sql insert with memo field truncates text data Graham Meredith General Discussion 1 September 1st, 2004 03:11 AM
Help Needed for Groups Please Paul Black General Discussion 15 June 21st, 2004 02:54 AM
Query truncates data Jake Running & Setting Up Queries 3 June 2nd, 2004 09:56 PM
Data Validation Query - How do I stop data being input in a cell once maximum characters are reached? Colin Worksheet Functions 1 January 4th, 2004 12:09 AM


All times are GMT +1. The time now is 01:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.