Queries can't show more than 255 characters - microsofts solution not working

[oliviar]

Hi Guys,
I need to add a query and a table together.
The new table has a field with way more than 255 characters in it.
Access hates it.

I tried their workaround to the letter, and no luck.
http://support.microsoft.com/kb/896950

The table is linked. I made a new query, first without the column. Then I made a new query, and added the column from the table. Then I used outer join between them... annnnnddd.... can't do it.

 

[Norie]

How are you trying to 'add' the table and query?

Are you creating a new table or appending the query to an existing table?

The link you posted says something about UniqueValues and DISTINCT.

Are you using UniqueValues?

 

[oliviar]

Hi Norie,
I am making a new query between the one I want an outer join on, and the one with 255+ characters, and I am joining them with an outer join based on just one shared value.

I don't know about distinct values etc. I saw that, but I don't know what it is or how to fix it (or if I want to?)

 

[Norie]

Do you want to only return unique/distinct values in the query?

If you don't set the UniqueValues property of it to No.

What is it you are trying to do anyway?

You mention 2 outer joins?

What fields are you joining on? Perhaps that's the problem and not the UniqueValues thing.

 

[oliviar]

One join. Two queries. And I know its this problem because as soon as I remove the field with the long values the query works.

How do I deselect distinct values?

 

[Norie]

Two queries?

I thought it was a table and a query.

Also it would help to know what field(s) you were joining on and what you are trying to do.

To 'deselect' DistinctValues right click the query in Design view and change the DistinctValues property to No.

However that might not fix the problem, because distinct values might not be the problem.

 

[oliviar]

Where is the distinct values, yes/no option?
I've clicked on everything. And googling it brings up only SQL solutions...



And it can be table and query, or query and query. It doesn't seem to make a difference, the problem remains, and it goes away as long as I delete that field.

 

[Norie]

I think it might be Unique Values not Distinct values.

If you right click a blank area of the table section of a query while in design view you should get a context menu with the Properties... option.

Selecting that should show a property sheet.

You can also reach it via the View menu on the main toolbar I think, or in Access 2010 you'll find Property Sheet in the Show/Hide menu of the Query Design ribbon.

By the way, you haven't actually said what the problem is.

Is Access throwing an error?

Is it preventing you executing the query?

Is it executed but you end up with incorrect results?

 

[xenou]

Hi Guys,
I need to add a query and a table together.
The new table has a field with way more than 255 characters in it.
Access hates it.

What do you mean by adding a query and a table together? It's not altogether clear what you are doing. How about posting your SQL statement and explaining if this is a SELECT query, an append query, is used in a form or report, and so on. Access can handle fields with more than 255 characters but this generally involves a working with a MEMO data type. It's not necessarily compatible with TEXT data types.

 

[oliviar]

I don't understand what you guys are asking. I don't do anything in Access except join tables together. Everything is some combination of join. I'm essentially using Access as a big VLOOKUP.

I am trying to merge two tables/queries based on the same key. Except one has a really long field and so Access is unhappy and keeps giving me this error.

No SQL, no fancy queries, no formulas, no macros. Everything is the default.

I am taking a linked table and a query and joining them together using an outer join (query is the longer table), and taking all the columns from each and plonking them into one result.

The field that's bothering access IS a text field. And it probably should be a memo field. But I can't seem to make it a memo field, because I can't change the properties of a linked table.

If I create a very simple query with just the linked table and nothing else, by taking everything from it, and putting it into a query... it works. It shows me the field. But as soon as I ask it to join to anything... BAM! 'field too long, blah blah blah'