Problems With Memo Fields When Running A Query In Access 2010

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?

  1. Change the Access memo field to a text data type but this would lose the remaining surplus of 255 characters that being the case.
  2. Simply say to the client “you don’t really need to sort on this field”. But they needed too!
  3. 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?

  1. In a query, place all the fields required including the Access memo field.
  2. 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).
  3. 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 https://AccessDatabaseTutorial.com.

From Ben Beitler – “Your Access Database Expert”

Article Source: http://EzineArticles.com/?expert=Ben_S_Beitler

Problems With Memo Fields When Running A Query In Access 2010running a query in access

The Memo data type was never really intended for running a query in Access as it is usually used for comments and narratives and can take a lot more memory to run and store.

To learn more about Data types and Access database planning and designing, take a look at my great eBook offers; there are currently six eBooks for a special bundle offer.

This entry was posted in Functions, MS Access, Queries and tagged , , , , . Bookmark the permalink.

2 Responses to Problems With Memo Fields When Running A Query In Access 2010

  1. Ben says:

    Memo datatypes are much longer and depending on version can be a fixed minimum of 65,000 plus characters.

  2. Elsa Branker says:

    Is memo a shorter version of a text maximum 535

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.