Memo fields displays only 255 characters

I have field in an Access 2002 database that is set to "memo" because it contains large amounts of text.

However, when I display the contents of the field on the web (using ASP vbscript) only 255 characters show up.

Here's an example: http://www.ihea.info/pages/details.asp?pubID=8

The paragraph "contents" stops at the word "thickening" even though there's more text inside the database field.

What could be wrong?
polaatxAsked:
Who is Participating?
 
jjafferrConnect With a Mentor Commented:
polaatx
It is very simple to check if the problem IS from the query, just open the qery and run it, then count your characters in it.
Away to go about this is to split the memo field into many 255 character fields, this is what I did to one of our Reports which was taking the value from such a query,
In the quey, assuming your field name is Contents, place each of the following lines in a field by itself:
Contents1: mid([Contents],1,255)
Contents2: mid([Contents],256,511)
Contents3: mid([Contents],512,767)
Contents4: mid([Contents],768,1023)
Contents5: mid([Contents],1024,1279)
Contents6: mid([Contents],1280,1535)
Contents7: mid([Contents],1536,1791)
Contents8: mid([Contents],1792,2047)
Contents9: mid([Contents],2048,2303)
Contents10: mid([Contents],2304,2559)

In order for you to be safe, you have to make you contentsNN bigger than a maximum number that you can use,
If the the values do not exist for the rest, you will have NULL/Empty value which will not hurt your calculations/formula,
the last thing you should do in your ASP (not in the query, but where you take the quey results to), put the ContenetsNN together/Concatenate like this (depending on how ASP does it):
Contents=Contents1 + Contents2 + Contents3 + Contents4 + Contents5 + Contents6......
OR
Contents=Contents1 & Contents2 & Contents3 & Contents4 & Contents5 & Contents6...

So what is happening here is,
we took the maximum characters of Contents the query gives for each filed,
then put them together in ASP

I hope this helps,

jaffer
0
 
jmcraigCommented:
If the Acces database is passing the memo field to a string variable on the ASP page then the limit is determined by the ASP page. Since a string variable can only hold 255 charcters by default then your memo field is still being truncated/cut.

Try these lines of code

Dim strMemoFieldData
strMemoFieldData = Space(1200)

You can then pass the Memo field data into the variable "strMemoFieldData"

0
 
jjafferrCommented:
Hi polaatx,

I think you are passing the data from your Table to ASP through a query,
in the query you have done grouping,
if grouping is used, it will ONLY get 255 characters.
So the way around this is to read your data from the Table after filtering it.

Hope this helps

Jaffer
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
polaatxAuthor Commented:
Jaffer, I am not sure my problem is what you describe or what jmcraig describes , or both.

Regarding your solution. Yes there was grouping done.

Here's my SQL right now:

SELECT *
FROM qryDetails
WHERE PubID = MMColParam

If you understand you correctly, it would be something like:

SELECT qryDetails.*, tblBrochures.description
From qryDetails, tblBrochures
WHERE PubID = MMColParam

but when I put this into Dreamweaver's Recordset wizard, Dreamweaver just takes out the mention of tblBrochures.description automatically because I guess it assumes it is already part of query.

Below is the SQL in the query. Can you please help me figure out what kind SQL to give Dreamweaver to incorporate your solution?

SELECT tblBrochures.PubID, tblBrochures.HardCopyAvailable, tblBrochures.PubCode, tblBrochures.PublicationPending, tblBrochures.Activation, tblLanguages.LanguageName, tblTitle.TitleName, tblSpecialPopulation.SpecialPopulation, tblDisease.DiseaseName, tblCreationStatement.creationStatement, tblTitle.description, tblBrochures.pdfLocation, tblBrochures.thumbnail, tblBrochures.DonationPriceSchedule, tblBrochures.DownloadAvailable, tblBrochures.Description, tblBrochures.PubDate, tblBrochures.RevisionDate, tblTitle.OriginalBrochureID, tblDownloadDonation.PriceUnder10, tblDownloadDonation.Price100, tblDownloadDonation.Price250, tblDownloadDonation.Price500, tblDownloadDonation.Price1000, tblDownloadDonation.Price5000
FROM tblDownloadDonation RIGHT JOIN ((tblCreationStatement RIGHT JOIN (tblLanguages RIGHT JOIN (tblTitle RIGHT JOIN (tblSpecialPopulation RIGHT JOIN tblBrochures ON tblSpecialPopulation.SpecialID = tblBrochures.SpecialPopID) ON tblTitle.titleID = tblBrochures.Title) ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblCreationStatement.statementID = tblTitle.creationStatement) LEFT JOIN (tblDisease RIGHT JOIN tblTitleDisease ON tblDisease.DiseaseID = tblTitleDisease.DiseaseID) ON tblTitle.titleID = tblTitleDisease.TitleID) ON tblDownloadDonation.DonationID = tblBrochures.DonationPriceSchedule
GROUP BY tblBrochures.PubID, tblBrochures.HardCopyAvailable, tblBrochures.PubCode, tblBrochures.PublicationPending, tblBrochures.Activation, tblLanguages.LanguageName, tblTitle.TitleName, tblSpecialPopulation.SpecialPopulation, tblDisease.DiseaseName, tblCreationStatement.creationStatement, tblTitle.description, tblBrochures.pdfLocation, tblBrochures.thumbnail, tblBrochures.DonationPriceSchedule, tblBrochures.DownloadAvailable, tblBrochures.Description, tblBrochures.PubDate, tblBrochures.RevisionDate, tblTitle.OriginalBrochureID, tblDownloadDonation.PriceUnder10, tblDownloadDonation.Price100, tblDownloadDonation.Price250, tblDownloadDonation.Price500, tblDownloadDonation.Price1000, tblDownloadDonation.Price5000;
0
 
polaatxAuthor Commented:
jmcraig,

How would I pass the Memo field data into the variable "strMemoFieldData"?

I tried the following, but got a "name redefined" asp error:

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)

strMemoFieldData =(rsDetails.Fields.Item("tblBrochures.Description").Value)
%>
0
 
jjafferrCommented:
polaatx
At least I know you have a problem from the query, as grouping will truncate the memo field to 255 characters.
let me guide you to another similar question.
There you will find at least 2 answers, 1 from me for using the Dlookup, and the other from nico5038,

My solution:
1- Don't read this memo field from the query,
2- in the control source of the memo field in the Report, use the Dlookup function (place the "where" conditions here) to get the memo info and use CanGrow too,

http://www.experts-exchange.com/Databases/MS_Access/Q_20898838.html

I hope this helps you further.

jaffer
0
 
jmcraigCommented:
Paste the code on the page up to the troublesome paragraph + 10 additional lines and I will take a look
0
 
polaatxAuthor Commented:
jmcraig,

Here's the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!--#include file="../Connections/IHEA2.asp" -->
<%
Dim rsDetails__MMColParam
rsDetails__MMColParam = "1"
If (Request.QueryString("PubID") <> "") Then
  rsDetails__MMColParam = Request.QueryString("PubID")
End If
%>
<%
Dim rsDetails
Dim rsDetails_numRows

Set rsDetails = Server.CreateObject("ADODB.Recordset")
rsDetails.ActiveConnection = MM_IHEA2_STRING
rsDetails.Source = "SELECT * FROM qryDetails WHERE PubID = " + Replace(rsDetails__MMColParam, "'", "''") + ""
rsDetails.CursorType = 0
rsDetails.CursorLocation = 2
rsDetails.LockType = 1
rsDetails.Open()

rsDetails_numRows = 0
%>
<%
Dim rsResults__mmreg
rsResults__mmreg = "3000"
If (request.querystring("reg") <> "") Then
  rsResults__mmreg = request.querystring("reg")
End If
%>
<%
Dim rsResults__mmdis
rsResults__mmdis = "3000"
If (request.querystring("sub")  <> "") Then
  rsResults__mmdis = request.querystring("sub")
End If
%>
<%
Dim rsResults__mmlang
rsResults__mmlang = "3000"
If (request.querystring("lang")  <> "") Then
  rsResults__mmlang = request.querystring("lang")
End If
%>
<%
Dim rsResults__mmspecpop
rsResults__mmspecpop = "3000"
If (request.querystring("spec")  <> "") Then
  rsResults__mmspecpop = request.querystring("spec")
End If
%>
<%
Dim rsResults
Dim rsResults_numRows

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_IHEA2_STRING
rsResults.Source = "SELECT tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, Count(tblDisease.DiseaseName) AS CountOfDiseaseName, Max(tblDisease.DiseaseName) AS MaxOfDiseaseName, tblLanguages.LanguageName, tblLanguages.LanguageID, Max(tblTitle.TitleName) AS MaxOfTitleName, Count(TblRegion.RegID) AS CountOfRegID, Count(TblDisease.DiseaseID) AS CountOfDiseaseID, Max(TblRegion.Region) AS MaxOfRegion, tblSpecialPopulation.SpecialID, Max(tblSpecialPopulation.SpecialPopulation) AS MaxOfSpecialPopulation, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  FROM ((TblRegion RIGHT JOIN (((tblTitle RIGHT JOIN (tblLanguages RIGHT JOIN tblBrochures ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblTitle.titleID = tblBrochures.Title) LEFT JOIN tblSpecialPopulation ON tblBrochures.SpecialPopID = tblSpecialPopulation.SpecialID) LEFT JOIN tblLanguageRegion ON tblLanguages.LanguageID = tblLanguageRegion.LanguageID) ON TblRegion.RegID = tblLanguageRegion.RegionID) LEFT JOIN tblDiseaseTitle ON tblTitle.titleID = tblDiseaseTitle.TitleID) LEFT JOIN tblDisease ON tblDiseaseTitle.DiseaseID = tblDisease.DiseaseID  WHERE TblRegion.RegID=" + Replace(rsResults__mmreg, "'", "''") + " or TblDisease.DiseaseID=" + Replace(rsResults__mmdis, "'", "''") + " or tblLanguages.LanguageID=" + Replace(rsResults__mmlang, "'", "''") + " or tblSpecialPopulation.SpecialID=" + Replace(rsResults__mmspecpop, "'", "''") + "  GROUP BY tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, tblLanguages.LanguageName, tblLanguages.LanguageID, tblSpecialPopulation.SpecialID, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  ORDER BY tblLanguages.LanguageName;"
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rsResults_numRows = rsResults_numRows + Repeat1__numRows
%>
<%
Dim rsAllTitles
Dim rsAllTitles_numRows

Set rsAllTitles = Server.CreateObject("ADODB.Recordset")
rsAllTitles.ActiveConnection = MM_IHEA2_STRING
rsAllTitles.Source = "SELECT LanguageID, LanguageName  FROM tblLanguages  ORDER BY LanguageName"
rsAllTitles.CursorType = 0
rsAllTitles.CursorLocation = 2
rsAllTitles.LockType = 1
rsAllTitles.Open()

rsAllTitles_numRows = 0
%>

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)

strMemoFieldData =(rsDetails.Fields.Item("tblBrochures.Description").Value)
%>
<%
Dim rsDetails__MMColParam
rsDetails__MMColParam = "1"
If (Request.QueryString("PubID") <> "") Then
  rsDetails__MMColParam = Request.QueryString("PubID")
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>IHEA: International Health Education Alliance</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
@import url(styles_IHEA.css);

-->
</style>

<SCRIPT LANGUAGE="javascript" SRC="scripts.js"></SCRIPT>

</head>

<body id="detailspage" onload="setBottom(self.screen.availHeight)"><!-- #BeginLibraryItem "/Library/masthead.lbi" -->
<div id="masthead"> <span><a href="../Library/home.asp"><img src="../images/logo/banner1.gif" alt="IHEA Homepage" width="450" height="70" border="0"></a></span>
<div id="menu">
<ul>
<li><a href="home.asp">Home </a></li>
<li><a href="mission.asp">About Us </a></li>
<li><a href="development.asp">Our Work </a></li>
<li><a href="support.asp">Support IHEA </a></li>
<li><a href="contact.asp">Contact Us </a></li>
<li><a href="emailupdates.asp">Email Updates</a></li>
</ul>
</div></div><!-- #EndLibraryItem --><div id="container">
<div id="sidebar">

<!--#include file = "forminclude.asp" -->
<hr color="#999" width="95%">
</div>
<div id="maincontent"><br>

<% 'if IsNUll (rsDetails.Fields.Item("thumbnail").Value) then%><%'else%>
<%'end if%>
<p><span class="header">Title: </span><%=(rsDetails.Fields.Item("TitleName").Value)%></p>
<p><span class="header">Language: </span><%=(rsDetails.Fields.Item("LanguageName").Value)%></p>
<p><span class="header">Subject: </span><%=(rsDetails.Fields.Item("DiseaseName").Value)%></p>
<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value) & "</p>"
end if %>
</p>

0
 
polaatxAuthor Commented:
jjafferr, thanks for your help. I checked out the link. The other quesiton has to do with pulling the data inside Access. I am not creating a report. I am pulling data from Access using ASP vbscript and displaying it on the web. So I guess I have to mess with my SQL to get the data directly from the table. my SQL appears below. I don't know if you can help in any way figure out how to alter it so I get the value fro tblBrochures.Description directly from the table.

rsResults.Source = "SELECT tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, Count(tblDisease.DiseaseName) AS CountOfDiseaseName, Max(tblDisease.DiseaseName) AS MaxOfDiseaseName, tblLanguages.LanguageName, tblLanguages.LanguageID, Max(tblTitle.TitleName) AS MaxOfTitleName, Count(TblRegion.RegID) AS CountOfRegID, Count(TblDisease.DiseaseID) AS CountOfDiseaseID, Max(TblRegion.Region) AS MaxOfRegion, tblSpecialPopulation.SpecialID, Max(tblSpecialPopulation.SpecialPopulation) AS MaxOfSpecialPopulation, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  FROM ((TblRegion RIGHT JOIN (((tblTitle RIGHT JOIN (tblLanguages RIGHT JOIN tblBrochures ON tblLanguages.LanguageID = tblBrochures.LanguageID) ON tblTitle.titleID = tblBrochures.Title) LEFT JOIN tblSpecialPopulation ON tblBrochures.SpecialPopID = tblSpecialPopulation.SpecialID) LEFT JOIN tblLanguageRegion ON tblLanguages.LanguageID = tblLanguageRegion.LanguageID) ON TblRegion.RegID = tblLanguageRegion.RegionID) LEFT JOIN tblDiseaseTitle ON tblTitle.titleID = tblDiseaseTitle.TitleID) LEFT JOIN tblDisease ON tblDiseaseTitle.DiseaseID = tblDisease.DiseaseID  WHERE TblRegion.RegID=" + Replace(rsResults__mmreg, "'", "''") + " or TblDisease.DiseaseID=" + Replace(rsResults__mmdis, "'", "''") + " or tblLanguages.LanguageID=" + Replace(rsResults__mmlang, "'", "''") + " or tblSpecialPopulation.SpecialID=" + Replace(rsResults__mmspecpop, "'", "''") + "  GROUP BY tblBrochures.PubID, tblBrochures.Activation, tblBrochures.PublicationPending, tblLanguages.LanguageName, tblLanguages.LanguageID, tblSpecialPopulation.SpecialID, tblBrochures.DownloadAvailable, tblBrochures.HardCopyAvailable  ORDER BY tblLanguages.LanguageName;"
0
 
jmcraigCommented:
Replace the code at the bottom of the section you posted that reads thus;

<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value) & "</p>"
end if %>
</p>


With code that reads so;

<p><span class="header">Contents: </span>
<% if IsNull(strMemoFieldData) then
response.write(rsDetails.Fields.Item("tblTitle.Description").Value) & "</p>"
else
response.write(strMemoFieldData) & "</p>"
end if %>
</p>


Hope fully this should set things right just as a precaution you may want to replace any occurrences of

rsDetails.Fields.Item("tblTitle.Description").Value

with

strMemoFieldData


Joshua




0
 
polaatxAuthor Commented:
Joshua, thanks for your reply.

First, I realized that the record I am interested in showing is the (rsDetails.Fields.Item("tblTitle.Description").Value),
not (rsDetails.Fields.Item("tblBrochures.Description").Value)

So I reversed what you gave me and put this on top of the page:

<%Dim strMemoFieldData
strMemoFieldData = Space(1200)
strMemoFieldData = (rsDetails.Fields.Item("tblTitle.Description").Value)
%>

And put this in the bottom:
<p><span class="header">Contents: </span>
<% if IsNull(rsDetails.Fields.Item("tblBrochures.Description").Value)  then
response.write (strMemoFieldData)  & "</p>"
else
response.write(rsDetails.Fields.Item("tblBrochures.Description").Value)  & "</p>"
end if %>
</p>

But it is still not showing more than 255 characters.

Here's the page as it appears on the web: http://www.ihea.info/pages/details.asp?pubID=41

What else could be wrong? Could the problem also be because it is grouped in a query, like jjafferr says?

I am leaving for Mexico first thing in morning. But i will check email from internet cafes and hopefully somehow try to implement any other solutions you might have. (Maybe I can use notepad to edit pages and then ftp it up using IE, I don't know) Thanks a bunch.
0
 
jjafferrCommented:
Hi Jack
My last comment will work.
Well, since I was new to Access, I faced this problem and solved it this way, and it is in use for more than 2 years already in our office application.
I am working on an update, so this will be tackeled differently.

thanks

jaffer
0
 
jjafferrCommented:
Thanks buddy, I already feel better.

Aren't you suppose to wear your Admin hat and say this ;o)

jaffer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.