TomBock2004
asked on
Query drops characters from MEMO field
I have a table to contains 2 memo fields (besides a bunch of text and number fields).
In one of the record's memo field, the "character count" is 555 characters (with spaces).
Now, once I created a query and inserted the 2 memo fields. In the query, I lose some characters lots of text.
For instance the record that has 555 characters now shows only 255 characters in the query.
I am vaguely familiar about about the 256 max character concept. However, that's why I chose "Memo" as data type for this field. I was under the impression I would have an unlimited amount of characters to enter.
Again, it's only the query that drops characters (not the table). Why is that? And how can I fix it in the
query?
Tom
In one of the record's memo field, the "character count" is 555 characters (with spaces).
Now, once I created a query and inserted the 2 memo fields. In the query, I lose some characters lots of text.
For instance the record that has 555 characters now shows only 255 characters in the query.
I am vaguely familiar about about the 256 max character concept. However, that's why I chose "Memo" as data type for this field. I was under the impression I would have an unlimited amount of characters to enter.
Again, it's only the query that drops characters (not the table). Why is that? And how can I fix it in the
query?
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I go about processing it via VBA?
Tom
Tom
TomBock2004,
dim strQuery as striing
dim strResult as string
dim rs as DAO.recordset
strQuery = "SELECT mymemofield from MyTable WHERE key='something'"
set rs = currentdb.openrecordset(st
strResult = rs!mymemofield
' strResult will contain the entire memo up to about 32k characters I believe
dim strQuery as striing
dim strResult as string
dim rs as DAO.recordset
strQuery = "SELECT mymemofield from MyTable WHERE key='something'"
set rs = currentdb.openrecordset(st
strResult = rs!mymemofield
' strResult will contain the entire memo up to about 32k characters I believe
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav:
Am I supposed to put this in a module? And then call it in the query via "LookupMemo"?
Tom
Am I supposed to put this in a module? And then call it in the query via "LookupMemo"?
Tom
ASKER
kmslogic & Gustav:
Going back to the "drawing board"... the reason for something data is truncated is caused by something else. Both of your version seemed to work fine in a testing query.
Then however, I plugged either suggestion into my actual query... this resulted in the same outcome.
Here's what's going on: If I create a basic query w/ only 2 fields (Autonumber and memo field), I show e.g. 100 records. Now, in my actual query I have included other fields that require me to select "Group By".
Once I apply the GroupBy in the query, I still show the 100 records (since I dropped the fields for testing purposes), but now the text in the memo field is truncated. So, again "Group By" (no matter what approach I'm using) truncates the data for each record.
Do you know of another workaround that will allow me to use GroupBy in the query?
Tom
P.S. Raised points to 500
Going back to the "drawing board"... the reason for something data is truncated is caused by something else. Both of your version seemed to work fine in a testing query.
Then however, I plugged either suggestion into my actual query... this resulted in the same outcome.
Here's what's going on: If I create a basic query w/ only 2 fields (Autonumber and memo field), I show e.g. 100 records. Now, in my actual query I have included other fields that require me to select "Group By".
Once I apply the GroupBy in the query, I still show the 100 records (since I dropped the fields for testing purposes), but now the text in the memo field is truncated. So, again "Group By" (no matter what approach I'm using) truncates the data for each record.
Do you know of another workaround that will allow me to use GroupBy in the query?
Tom
P.S. Raised points to 500
ASKER
I figured it out...
Although I'm using GroupBy, I now changed it to "First" for the Memo field. Now data is not being truncated.
Thanks anyhow. Your other methods are still worth some points to me.
Tom
Although I'm using GroupBy, I now changed it to "First" for the Memo field. Now data is not being truncated.
Thanks anyhow. Your other methods are still worth some points to me.
Tom
MemoTxt: LTrim([YourMemo])
doesn't help, you will have to use VBA to look up those memos.
/gustav