Combined memo fields of several queries into one returns a string

[inso18]

Hello people.

Have anyone come across this:

I have a query that combines memo fields of several tables that are joined by a common denominator field into MemosComb field of qryComb query.

Table1... Table2... Table3...........qryComb
Memo1.....Memo2.....Memo3............MemosComb
where MemosComb: [Memo1] & [Memo2] & [Memo3]

qryComb is a record source of a report. When I ran the report the MemosComb field doesn't show all the lenght of the text of the combined memos but cuts some of it, and shows only a part of it which I figure is of a lenght of a 255 string.

When I try to run a make table query outta qryComb I too get 255 text fields instead of memos - maybe it is related to the report problem?

Any suggestions or thoughts on this?
How can I make the report show the entire length of the memo?
Much thanks, inso

 

[dhookom]

Do you mind sharing your SQL view(s)? It might be an issue where your SQL contains "DISTINCT", "DISTINTROW", "GROUP BY", or "UNION".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[inso18]

Here you go:

This is the combining query:

SELECT tblHotel.HotelId, [Remarks1200t] & [Remarks1300t] & [Remarks1321t] & [Remarks1322t] AS Remarks
FROM (((qryEntRemarks1200t RIGHT JOIN tblHotel ON qryEntremarks1200t.HotelId = tblHotel.HotelId)
LEFT JOIN qryEntRemarks1300t ON tblHotel.HotelId = qryEntRemarks1300t.HotelId) 
LEFT JOIN qryBidurRemarks1321t ON tblHotel.HotelId = qryEntRemarks1321t.HotelId) 
LEFT JOIN qryEntRemarks1322t ON tblHotel.HotelId = qryEntRemarks1322t.HotelId;

This one of the qryEntRemarks1**t queries:
the 4 of them look the same, except the difference in the 1***t number

SELECT tblHotelData.RoundId, tblHotelData.HotelId, 
tblHotelData.QuestionId, tblHotelData.Remarks AS 
Remarks1200t
FROM tblHotelData
WHERE (((tblHotelData.HotelId)<>99) AND
((tblHotelData.QuestionId)="1200t") AND
((Len([Remarks]))>"1"));

 

[dhookom]

The Len() function returns a number, not a string:
[tt][blue]Len([Remarks])>1 [/blue][/tt]

If you view the datasheet view of the report's record source, do you see more than 255 characters in any field/record?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[inso18]

The Len() function syntax is a typo, although it somehow did correctly eliminated empty strings. (Maybe it had compared them lithographically - as "a" is smaller than "b". I've changed it to > 1 and it works the same. Thanks for making me notice though.

On the datasheet view of the report's record source I see larger than 255 character cells, some are up to ~ 500.

 

[inso18]

I also must notify that with a regular query that has
bigger than 255 characters records the records do appear fine on the report.

 

[dhookom]

What section of the report are you displaying the memo field?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[inso18]

The first grouping, and the records are being displayed on the footer.

 

[dhookom]

This is usual behaviour when the memo fields aren't bound to text boxes in the detail section.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[inso18]

So if the memo fields are in the footer section, they would be cut off to 255 characters?

Is there a work around this?

 

[dhookom]

Have you tried moving them to the detail section to see if the total text would appear? If this works, leave them there. If you can't leave them in the detail section, consider using a subreport for the details.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]