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]

You can't do anyhing with queries without SQL being involved, especially something involving joins.

You may not see the SQL but it's there and the one way we might get some idea of what you are trying to do is to see it.

If you just click SQL or goto View>SQL in query design mode you'll see the the SQL for the query.

What data type are the fields you are 'joining'?

It's just not possible to join on fields of some data types and just not wise to do so on some.

One type of field it's not wise to join on are text fields, it is possible though.

If you can't change the properties of the linked table you could make a new table from it.

SELECT * FROM LinkedTable INTO MyNewTableNotLinkedAnymore

You should now be able to

 

[oliviar]

Hi Norie,
The only two fields I ever use in my joins are the same 10 digit numbers. The remaining fields with the info in them are numbers and text.

The linked tables have to stay linked because they are updated by teams of people throughout the day. If I start making unlinked tables manually, I might as well use VLOOKUP everytime I need these files joined. It would take the same amount of time.

Is there an automatic way to create an unlinked table on demand, that always has the same name and properties? That way, when I run the query, it can refresh the linked table, make the new table, run the query, and be perfectly up to date.

 

[xenou]

It might help to view the SQL query you are actually using (as Norie explained how to do). Not sure. Some joins would work but others may not work. Also if the linked table has TEXT data types the problem may be in the original table - a TEXT data type can't hold more than 255 characters in Access. So this doesn't really make sense that you say its TEXT in the original table.

You can create a copy of the table by just running a make table query (also described above by Norie) ... it can't have the same name though, because you can't have two tables with the same name in a database. Though on second though if you want to change the datatype from text to memo, you may want to have a copy of the table that you first clear of records, then add (append) the latest records into. That is, if you decide to go this route. That way your copy of the table will have the Memo field that you are trying to get here.

 

[oliviar]

I don't want to paste my sql, because its MASSIVE. Combined, the two tables have 150 columns.

In the linked table, Access has no trouble showing me the whole field. None. It also says in the design properties that its 'text'. That why I say its 'text'. Also if I create a query from it, on its own, it also works. Shows me the whole field, no errors. Although I don't know whether Access is storing it as Memo or Text in this basic query.

However it puzzles me why it would start as a text field, work, move to my basic query, also as a text field, work, then DIE on me when I use a join query.

Is there a way to FORCE a column in a query to format itself as 'memo'?

 

[xenou]

This sounds like a red herring here - it's probably not this field at all but something else about your query. Text fields don't hold more than 255 characters to begin with.

Are you really able to remove just this one field from the query and get all the rest of the information? What if you leave this one field in and remove another field or two?

Note: what kind of database are you linked too?

 

[Norie]

If removing the one field makes things work then why not do it?

If you actually need that field then it might be able to add it later - you don't need to do everything in one go.

In fact it's not a good idea to do so.

One reason for that is because you could end up with complex, confusing queries that are difficult to debug.