CODEWITHSUNDEEP
sqlms-accessjoinms-access-2010
redracer67
redracer67

Reputation: 21

ACCESS: Exceed 255 character minimum

I have discovered that I am not pulling in all of the characters for some records from a relationship in one of my fields.

For one field, the query is only pulling the first 255 characters.

After some research, I found that changing the field type from Text to "Memo" allows for more characters in a field. Unfortuntely, when I run my query I then receive an error saying "Memo" cannot join (which makes sense since memo has the potential to join gigabites worth of data).

Is it possible to use a field with a Memo type in my query? I found that using "Left(blah.table,255) would be the inner join work around, but I seem to time out my query when I add this change.

A summary of the many comments below by a suggestion from guitarthrower:

Clarification #1: I do not have control on how I receive the data and the format I must export it in (bringing it into and out of Excel)

Clarification #2: The Query does exactly what I need accomplish for all field <255 characters (the majority). For the ~50 rows of data that are >255, I need to edit my query to capture all of it.

Clarification #3: Upon further analysis of my query, I am able to use a query similar to Allen Browne's concat function and I do have results that are >255 characters using this function in Access, but when I use this query with another one, Access truncates the results to =255 characters.

Clarification #4: I am using a combination of joining multiple columns together and a function "conc" that is similar to Allen Browne's function. This must be done because of the nature of table. The concate function is used to combine multiple rows of data into a single row. I have no control on how this data looks before Access (about 50,000 rows): 

Before (Source 1):
1    joey    Cake
1    joey    apple
2    Pam     orange
2    Pam     pear

After (Group 1): 
1 Joey  Cake:apple
2 Pam   orange:pear

This Group 1 is then combined with another set of data from another source in a system I have zero control in. 

Source 2: 
1 Joey water:paper
2 Pam  phone:tape


End result: 
1 Joey  Cake:Apple:water:paper
2 Pam   orange:pear:phone:tape

The "end result" is then truncated for some people.

However, the data from "Group 1" has some results that do exceed 255 and not trunacted in any way. Remember, Group 1 is formed from my "conc" function.

I have fully developed my queries to give me the "end result", but I need a way to exceed 255 characters as mentioned many times. I hope these clarifications are sufficient.

Upvotes: 1

Views: 573

Answers (1)

Grommit
Grommit

Reputation: 165

I use memo fields all of the time. If your query isn't working, try using FIRST with the memo, if that is possible. That gets around some of the restrictions.

Upvotes: -1

Related Questions