Union query: Field Size is Too Small

[dweeb]

I have a union query that was working last week, but now it's giving me an error 'the field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data'.

The offending fields in the origin tables are memo fields. By default they only accept 254 characters.

On a union query, are the destination fields supposed to be the same datatype as the originating table fields?

If my originating table contains the data, why is the query now telling me that the field size is too small?

Thanks for your help.

 

[Len Boorman]

With a UNION query all fields must be of the same data type and also preferably the same size, They also need to be in the same order within the Select part of the statement...I believe. The items mentioned I always strictly adhere to

len B

 

[dweeb]

With a UNION query all fields must be of the same data type and also preferably the same size, They also need to be in the same order within the Select part of the statement...I believe. The items mentioned I always strictly adhere to

len B

I realize that and they do. This query was working on Friday of last week.

Now I'm getting this error that the field size is too small. My tables are identical in structure and everything is ordered properly in my select statement.

This is very frustrating, as I cannot figure out why I'm getting this error.

Thanks for your suggestion.

 

[Len Boorman]

The offending fields in the origin tables are memo fields. By default they only accept 254 characters..

Okay so the basics check out but I am curious about the above statement. memo fields can accept much more that 254 chars.

I did not think that under UNION rules that a memo field was reduced to 254.

I maybe suspect that the first select statement calls a text field that is specified as say 50 chars and a later select is calling a text field that is spec'd at something greater than 50 and indeed has more than the first text field length. Hence teh message tha tthe field is too short
I think that the fields accept the size limitations according to the first select statement.

Len B

 

[dweeb]

At issue with the memo fields is I'm using linked tables to an Oracle database. I cannot force the memo field size to 64K, so the system defaults and truncates my data to 254 chars.

Again, my source tables are identical. Each datatype is the same, as are the lengths for my text fields.

Is this an issue because of the linked tables?

Thanks

 

[Len Boorman]

would not have thought linked tables was the problem. Okay maybe slow things up a bit but so what.

Think I would chop the UNION up into make table queries so that I could actually see the data and try to get some clues from that.

Either that or again chop up the UNION and run it in bits until I found the offending Select and examine that data carefully.

Is it possibele that previous memo fields did not actually contain 254 chars and then along came one that did. Just thinking out loud at the moment

len B

 

[dweeb]

There's definitely more data there than there was last week, but I don't think that's the issue.

I'll try removing fields from my query to see where that leads.

Thanks for the tip.

 

[Len Boorman]

Good Luck

L

 

[Pat Hartman]

I'm guessing that this is a data issue. This week the memo field in one of the tables has more than 255 characters.

The first table in the union dictates the column attributes for the recordset. Remember a recordset contains some number of IDENTICALLY formatted rows. Row 3 can't have a wider column than the rows above it. If you union tables containing memo fields, I'm not sure you end up with a recordset containing a memo field. If you end up with a fixed width column, Jet might have assumed a smaller width based on the first n rows of the recordset. Then when it came upon a wider column it choked.

 

[dweeb]

I'm guessing that this is a data issue. This week the memo field in one of the tables has more than 255 characters.

The first table in the union dictates the column attributes for the recordset. Remember a recordset contains some number of IDENTICALLY formatted rows. Row 3 can't have a wider column than the rows above it. If you union tables containing memo fields, I'm not sure you end up with a recordset containing a memo field. If you end up with a fixed width column, Jet might have assumed a smaller width based on the first n rows of the recordset. Then when it came upon a wider column it choked.

That's a great thought. Let me try to rearrange the order of my select statements with the table with the longest memo field first.


On edit, this did not work either. I'm going to have to go around this a different way.

Eliminating one memo field got the union query to work, but I need the data in the eliminated field.



Thanks for all of your help!

 

[Pat Hartman]

You can create a dummy table with a single row that has the memo field filled with 256 characters. Then union this table first to set the widths correctly. You can then create another query that selects all but that row from the union query.