Access query field limited to 255 characters

[stringman]

Hello,

I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.

I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.

Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.

Thanks in advance.

Ken

 

[neileg]

Why don't you concatenate it in the report instead of the query?

 

[stringman]

I'll give it a try but I'll still have to open a recordset and manualy create the query. Still less messy than creating a table and then creating the query. I guess I could stuff a concatenated variable into the report text box during the report onload event.

When I get results, I'll post. Thanks.

Ken

 

[Moniker]

It almost sounds like you're trying to do too much conditional If..Then type stuff in one field. Can you post what that looks like? I'm just curious.

 

[stringman]

Not a single IF. The query field contains the following:

Text: "(" & [qryWeekInReview]![Classification] & ") Task: " & [qryWeekInReview]![Task] & "; Impact: " & [qryWeekInReview]![Impact] & "; Org: " & [qryWeekInReview]![Org] & "; Personnel: " & [qryWeekInReview]![Personnel] & "; POC: " & [qryWeekInReview]![User]

Ken

 

[neileg]

I'll give it a try but I'll still have to open a recordset and manualy create the query. Still less messy than creating a table and then creating the query. I guess I could stuff a concatenated variable into the report text box during the report onload event.

When I get results, I'll post. Thanks.

Ken

Don't understand. Surely all you need is to ensure the fields are in the query on which the report is based and then concatenate in a text box.

 

[stringman]

neileg, you are correct.

I don't do reports often and when I looked at the reports text box properties, there were no "Events" associated w/ the text box. Therefore, I assumed I had to build the expression elsewhere. However, after neileg said to build the expression in the report I noticed the expression builder ellipse next to the text box control source. So I built it there and everything works as it should.

Thanks to eveyone for your help.

Ken