Char limit in query for memo fields.

[bbarr]

I have a SELECT query where one of the fields is a memo field. One of the records has a rather large about of text in it's memo field.

When I run the query on that row, the field only shows a portion of the text.

Is there a maximum character output limit per field in a query even for a memo field AND is there a way to deal with this?

Thanks.

 

[SgtJarrow]

Although working from the top of my head here...I think I can interject some decent throught processes...

First, as to the limit of a memo in a query, I believe it is 255....maybe not, but sounds right.

Second, how to get around this. Depending on how the data is being displayed, I can think of a workaround. If you are using a non-continuous form or a report, DO NOT include the memo field in your query. On the non-continuous form, include an unbound field and make it large enough to show some of the data. You also may want to set the scrollbar properties of the field to accomodate longer data. The, using the oncurrent event of the form, simply open a recordset to the table where the form data is stored, findfirst the record id, and populate the unbound memo display field with the memo. The report setup would be similar but you would use the onformat event of details section to do the recordset searching and population.

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[jrbarnett]

Hi

Robert is correct in that SELECT on a memo field will only retrieve the first 255 characters. One way around this is to use DLookup function in your query with the key as a parameter to retrieve the current row.
I know it goes against good programming practise calling VBA from SQL, and is significantly slower, but is the best workaround I have found for this limitation.
Something like this will do:

SELECT Table.ID, DLookup ("Memofield", "Table", "ID=" & Table.ID)
FROM Table
ORDER BY Table.ID

John

 

[bbarr]

Thanks Rob,

So let me see if I understand what you are saying.

Report is rptReport1.
Memo field is txtMemo
The source of the data for txtMemo is in a table called tblMemos.

The report will have the value in the row idNotes = 100 from tblMemos.Note.

So in rptReport1's Detail_Format event I write some code that get's this value and then places it in txtMemo.

Does this sound right?

- Bruce

 

[bbarr]

Thanks John for your feedback too.

What about if I just use DLOOKUP in the report itself so let's say in my textbox on the report I have the data source = DLookup ("Memofield", "Table", "ID=" & Table.ID)

Is there any difference between the two methods?

Thanks again.

- Bruce

 

[jrbarnett]

Bruce

That again is a method of doing it, but remember that the report then the report gets a background module, which will slow it down; its exact performance hit will depend on the database situation.
I would try both methods and work out which is best for you.

John

 

[bbarr]

thanks!
- Bruce

 

[SgtJarrow]

Agreed...both solutions should produce the same results. DLookup is slow inherently, but in this instance so would be my suggestion. Try one, see howit works, and if it too slow, try the other...let us know so we can offer more help.

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com