query truncate memo

[abezuide]

Hi to the Gurus,

I have a table with a memo field. Then a very complex union query with three parts to a report. My problem is that the query truncates the memo field to 255 characters. Does anyone have a clue how to solve this?

Thanks,
Andrea

 

[WayneRyan]

Andrea,

I think that's the nature of MakeTable and Union queries. I did
a little research and there's no real answer.

A work-around is to do something like (Query1):

Select Mid(Memo,1,255) as strA, Mid(Memo,256, 510) As strB ... From YourTable
Union
Select Mid(Memo,1,255) as strA, Mid(Memo,256, 510) As strB ... From YourOtherTable

Then you can put them back together (Query2):

NewField: strA & strB

Clumsy, primitive, BUT functional if your memos aren't too long.

An alternative is to use VBA, I don't know exactly what you are doing, but
it might be an option.

Wayne

 

[abezuide]

Hi and thanks for the work around. What I am doing is: I pull data from a kinda huge Oracle database where memo fields can be rather lengthy. I then put it in a user friendly format in access for the user. From here I pull data for a report through this union query. The three parts is
a. Select date, memo, number,heading etc. from table a, table b, table c where a = b and b = c and date between user input and etc.
union
b. select date, memo, number,heading etc. from table a, table b, table d where a= b and b=c and number not in(select number from table a) and date between user input and etc.
union
c. select date, null, null,heading etc. from table a, table c where not date between user input (to display a heading with no data)

I use this union to base my report on. When executing the report, I create an external database with a table holding the data for the report, then export the report as well so that the user can manipulate the data and the report without messing up the data in the main database. This external database is created with vba using tabledefs etc.

The problem is with the first query at the moment, because when I look at the data pulled from the query, the memo fields are already cut off. However, I suspect that I am going to have the same problem when I export the data.

Thanks for your input, I will try to concat the strings, I think I will need to line 10 of them up in most cases

 

[abezuide]

Hi Wayne,

So much thanks for your research. Just wanted to let you know I came up with some sort of solution. The query for the report now looks like this:

The union query is the base of the second query. The memo fields come from the table which is also in the display but only the memo fields come from there. I join the primary keys and there you go! All the memos displayed in full!!!!!!!!

Thanks again!

 

[WayneRyan]

Andrea,

You're a genius. I wanted to find a way to "drag" them through the
whole process.

Well done,
Wayne

 

[abezuide]

Thank you so much for the kind words Wayne, BUT, unfortunately Murphy was into the third query, ha ha!. Well, it would have worked IF it wasn't for the third part of the union that excluded everything that was in the first two parts. What I had to do was make three seperate queries getting all my data. Then in some code putting the data into a temp table and base the report on the temp table sigh....... well, at least I got all my data.

Now, I do have one additional problem. Some of these memo fields are extremely long. The client wants to see the header which the data is sorted by on each page. Well, setting the header to repeat the section was no problem BUT, suddenly the report just hangs, because the memo field flows over to the next page and it is not yet a new section, so it doesn't know what to do with it. I set my font to minimal for the memo field and that seems to work, but I'm not sure the client is going to be so happy with a size 6 font for the memo field lol. Do you have any solutions for this problem?

Thanks a bunch,
Andrea