Problems With Memo Fields When Running A Query In Access 2010

I recently wrote an EzineArticle about the Memo fields when running a query in Access and what has changed with the latest version (2010).

It can affect criteria and causes sorting challenges and should be avoided if at all possible. But if you really need it, then read on and know your options…

Creating Microsoft Access Queries – Why You Should Avoid The Memo Data Type In Access Queries

I recently worked on a migration Access database project converting from version 2003 to the current 2010 and as part of my testing I made sure that all macros and the Access VBA code threw no errors (exceptions) and that all workflows were, well flowing.

After handing over the database to my client, I receive a call a couple of days later saying that one form weren’t sorting correctly. On a closer inspection, it was an Access memo field that seemed to not allow any form of sorting either via the standard sort command buttons or even via a query causing the outcome to not sort properly.

In Access 2003 it worked just fine but the latest version (2010) to my surprise, it had changed.

So what were my options?

Change the Access memo field to a text data type but this would lose the remaining surplus of 255 characters that being the case.
Simply say to the client “you don’t really need to sort on this field”. But they needed too!
Find a way to sort on the Access memo field, which was the only outcome here.

I created a query and simply applied the sort ascending option to the Access memo field and thought this would do the trick. But to my surprise, it didn’t sort properly not even by thirst 255 characters which would have been good enough.

I compared this and sorted it in an Excel spreadsheet to make sure it wasn’t me going around the twist!

The solution?

In a query, place all the fields required including the Access memo field.
Add a new calculated field and use the LEFT function for the full 255 character length so it would look something like Notes Sort: Left([Notes],255).
Set this field to sort in ascending order – and presto!

The LEFT function encapsulates and converts to a real text data type acting as the key field, which it sorts on.

Another Tip for you! Keep the Access memo field and the OLE data type field to a minimum in your table designs as they can absorb a lot more memory that is required and cause performance challenges. You can always use on the string functions to handle the Memo field.

I invite you to keep up to date with my articles and eBooks (More Access Database queries) which covers a lot of details and can be found at http://AccessDatabaseTutorial.com.

From Ben Beitler – “Your Access Database Expert”

Article Source: http://ezinearticles.com/expert/Ben_S_Beitler/840635