Query Is Limiting Characters It Displays

[jereece]

I am using Access 2007. I am quering an SQL database so the tables are linked. I have 2 queries. One query limits the characters in the "description" field but the other does not. I verified it's the query not the report associated with the queries. The main difference in the queries is the one not displaying all the text has one extra table linked in the query and I am displaying totals in order to perform a count in a different field. The properties of the "Description" field are identical in both queries. The Property Sheet settings appear to be the same for both queries.

Is there a setting somewhere in the Query that would limit the number of characters it can display?

Thanks for the help.

Jim

 

[vbaInet]

1. What is it limiting the characters to? Excact number of characters.
2. Is the description field a MEMO field?

 

[jereece]

The query with the problem is limiting characters to 255 for the Description field. The query without the problem shows all the characters in the description field. This field is unlimited in the SQL Database so it's like a Memo field.

 

[vbaInet]

Here's a link on memo fields:

http://allenbrowne.com/ser-63.html

 

[jereece]

vbaInet - I thought the info on the page you referenced was the answer but was wrong. This all started when I added Total on one of my fields so I was sure Aggregation was the issue. However I set my 2 memo fields to "First" instead of Group and ran the query. The 2 memo fields are still limiting text to 255 characters. Uniqueness and Format Property is not a problem with my query. I don't believe the others are issues but will have to look in more details. If you can think of anything else, I appreciate your input.

Thanks,
Jim

 

[vbaInet]

Further split the table into two parts, one to store your memo fields and the other for the other fields. Run the totals query on the fields without the memo and you can easily reference the memo using the ID from the other table.

 

[jereece]

Thanks. I'll give that a try tomorrow. Do you think this would speed up the query? When I run any query that uses Total in a field it seems to really slow down the query. If not, do you have any suggestions on how to speed it up?

As always, I appreciate the help.

Jim

 

[vbaInet]

If you have hundreds of thousands of records in a query that has inner subqueries then it will slow things up a bit. There would be a noticeable improvement without the memo field in the aggregation.

Good luck for tomorrow.

 

[jereece]

vbaInetv - No luck. The first query which pulls the Memo fields has all the characters in the "Description" field. However the second query which has the Total fields cuts off the referenced Memo field text to 255 characters. I have the "Description" field in the second query set to "First" instead of "Group By".

Any other suggestions? Thanks,
Jim

 

[vbaInet]

I think you misunderstood the approach. You split your table into two parts. One that will contain all the fields minus the memo fields and the other that will contain JUST the memo and a reference to the other table as a foreign key. Don't include the memo fields in your totals query.

Once you've done the totals query, you would create another query combining the totals query and the memos query.