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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Truncating of memo fields
I have a database in which the text of a memo field is
the core of the database. What has been placed in that field is of prime concern to the users, the other data around it while not peripheral is secondary to the data in the memo field. Therefore, they can search in whole or in part on that field. The problem is that when they want to send the results of the search to 1) a report 2) to Word 3) as a query the memo field is truncated which makes this whole exercise redundant. Does anyone know how to overcome this? Thank you |
#2
|
|||
|
|||
Truncating of memo fields
Robin wrote:
I have a database in which the text of a memo field is the core of the database. What has been placed in that field is of prime concern to the users, the other data around it while not peripheral is secondary to the data in the memo field. Therefore, they can search in whole or in part on that field. The problem is that when they want to send the results of the search to 1) a report 2) to Word 3) as a query the memo field is truncated which makes this whole exercise redundant. Not sure what you mean by "send the results", but the first thing you need to do is get a query to return the entire memo field. If the query does anything that requires the memo field to be compared, it will be truncated to 255 characters. The query operations that perform a compare operation are DISTINCT, GROUP BY, ORDER BY, UNION, etc. If your query uses one of those keywords, try to find a way to avoid it, use an alternative the memo field (use First instead of Group By, Left in the Order By, UNION ALL, ???) If that doesn't get you goping, post the query so we can see its exact syntax. Setting a memo field's Format property will also truncate the returned results. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Truncating of memo fields
Marsh
Thanks for your reply. The data is not being compared in the memo field although that field can be queried on a string expression input by the user in a form that will then display the results of the query in a list box. I found a work around that consists of the following. A listbox on the form displays the results of the original query. (This is a 'query by form' exercise). I use selected primay/foreign key data from this listbox to run a seperate query that creates the table which is the report's recordsource. This temporary table is created when the user clicks the button on the form "Reports" (a little more complex than that but I hope you get the gist). I have found that no truncation occurs when this happens. However, I have found another limitation in that if I try to run this report with many records ( 150 it appears), Access crashes and burns as it tries to format the report. It could be memory limitations on my PC (512Mb) or it could be that it just overloads Access. Anyway, the upshot of it is that I get the 'Send Report to Microsoft' error and the database crashes and burns. Any clues on how to overcome this problem other than trapping the error if the listcount of the list box is 150? Once again, thanks for your input. Robin -----Original Message----- Robin wrote: I have a database in which the text of a memo field is the core of the database. What has been placed in that field is of prime concern to the users, the other data around it while not peripheral is secondary to the data in the memo field. Therefore, they can search in whole or in part on that field. The problem is that when they want to send the results of the search to 1) a report 2) to Word 3) as a query the memo field is truncated which makes this whole exercise redundant. Not sure what you mean by "send the results", but the first thing you need to do is get a query to return the entire memo field. If the query does anything that requires the memo field to be compared, it will be truncated to 255 characters. The query operations that perform a compare operation are DISTINCT, GROUP BY, ORDER BY, UNION, etc. If your query uses one of those keywords, try to find a way to avoid it, use an alternative the memo field (use First instead of Group By, Left in the Order By, UNION ALL, ???) If that doesn't get you goping, post the query so we can see its exact syntax. Setting a memo field's Format property will also truncate the returned results. -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
Truncating of memo fields
Robin wrote:
Marsh I don't think it's the number of records that causes a crash. There are just too many instances of reports with many thousands of records with memo fields that are run routinely without problems for that to trigger a bug by itself. I've never run into it myself, but I've heard that having a lot of images in a report might do it, but you never mentioned using image/picture controls. I guess this might be a case of some kind of corruption, either in the data tables or in some form/report/module, but there's no good way to determine it. You might want to take a look at Tony's FAQ on corruption causes and cures to see if anything there makes the problem go away. http://www.granite.ab.ca/access/corruptmdbs.htm -- Marsh MVP [MS Access] The data is not being compared in the memo field although that field can be queried on a string expression input by the user in a form that will then display the results of the query in a list box. I found a work around that consists of the following. A listbox on the form displays the results of the original query. (This is a 'query by form' exercise). I use selected primay/foreign key data from this listbox to run a seperate query that creates the table which is the report's recordsource. This temporary table is created when the user clicks the button on the form "Reports" (a little more complex than that but I hope you get the gist). I have found that no truncation occurs when this happens. However, I have found another limitation in that if I try to run this report with many records ( 150 it appears), Access crashes and burns as it tries to format the report. It could be memory limitations on my PC (512Mb) or it could be that it just overloads Access. Anyway, the upshot of it is that I get the 'Send Report to Microsoft' error and the database crashes and burns. Any clues on how to overcome this problem other than trapping the error if the listcount of the list box is 150? Once again, thanks for your input. Robin -----Original Message----- Robin wrote: I have a database in which the text of a memo field is the core of the database. What has been placed in that field is of prime concern to the users, the other data around it while not peripheral is secondary to the data in the memo field. Therefore, they can search in whole or in part on that field. The problem is that when they want to send the results of the search to 1) a report 2) to Word 3) as a query the memo field is truncated which makes this whole exercise redundant. Not sure what you mean by "send the results", but the first thing you need to do is get a query to return the entire memo field. If the query does anything that requires the memo field to be compared, it will be truncated to 255 characters. The query operations that perform a compare operation are DISTINCT, GROUP BY, ORDER BY, UNION, etc. If your query uses one of those keywords, try to find a way to avoid it, use an alternative the memo field (use First instead of Group By, Left in the Order By, UNION ALL, ???) If that doesn't get you goping, post the query so we can see its exact syntax. Setting a memo field's Format property will also truncate the returned results. |
#5
|
|||
|
|||
Truncating of memo fields
Thanks Marsh,
I think it could be the dodgy hardware routine even 'though I'm meant to have a state of the art machine as I've had problems with Access over the last year with this machine even though colleagues of mine using the same machines don't get them while developing in Access. Thanks for the URL. It sure is a comprehensive site. Robin -----Original Message----- Robin wrote: Marsh I don't think it's the number of records that causes a crash. There are just too many instances of reports with many thousands of records with memo fields that are run routinely without problems for that to trigger a bug by itself. I've never run into it myself, but I've heard that having a lot of images in a report might do it, but you never mentioned using image/picture controls. I guess this might be a case of some kind of corruption, either in the data tables or in some form/report/module, but there's no good way to determine it. You might want to take a look at Tony's FAQ on corruption causes and cures to see if anything there makes the problem go away. http://www.granite.ab.ca/access/corruptmdbs.htm -- Marsh MVP [MS Access] The data is not being compared in the memo field although that field can be queried on a string expression input by the user in a form that will then display the results of the query in a list box. I found a work around that consists of the following. A listbox on the form displays the results of the original query. (This is a 'query by form' exercise). I use selected primay/foreign key data from this listbox to run a seperate query that creates the table which is the report's recordsource. This temporary table is created when the user clicks the button on the form "Reports" (a little more complex than that but I hope you get the gist). I have found that no truncation occurs when this happens. However, I have found another limitation in that if I try to run this report with many records ( 150 it appears), Access crashes and burns as it tries to format the report. It could be memory limitations on my PC (512Mb) or it could be that it just overloads Access. Anyway, the upshot of it is that I get the 'Send Report to Microsoft' error and the database crashes and burns. Any clues on how to overcome this problem other than trapping the error if the listcount of the list box is 150? Once again, thanks for your input. Robin -----Original Message----- Robin wrote: I have a database in which the text of a memo field is the core of the database. What has been placed in that field is of prime concern to the users, the other data around it while not peripheral is secondary to the data in the memo field. Therefore, they can search in whole or in part on that field. The problem is that when they want to send the results of the search to 1) a report 2) to Word 3) as a query the memo field is truncated which makes this whole exercise redundant. Not sure what you mean by "send the results", but the first thing you need to do is get a query to return the entire memo field. If the query does anything that requires the memo field to be compared, it will be truncated to 255 characters. The query operations that perform a compare operation are DISTINCT, GROUP BY, ORDER BY, UNION, etc. If your query uses one of those keywords, try to find a way to avoid it, use an alternative the memo field (use First instead of Group By, Left in the Order By, UNION ALL, ???) If that doesn't get you goping, post the query so we can see its exact syntax. Setting a memo field's Format property will also truncate the returned results. . |
Thread Tools | |
Display Modes | |
|
|