extra long concatenation

[btappan]

I am putting together a query that pulls together many fields from a database. One of the fields is a HTML description field that is considerably long. I've done something like this before where I just used "&" to string everything together, but it didn't seem very eloquent having to do it every 255 characters or so. I also believe I've run up against the maximum expression character limit (1024?) a few times in the past. It's been a while since I've tried this. Is there a more eloquent way to do this?

[ranman256]

A memo field will hold thousands of characters.
but dont see the need to concatenate. You can add all the text as is.

[btappan]

I'm pulling data from other tables through a query. The HTML Description I'm trying to write has many fields pulled in from other fields in the DB. Basically to fill in blanks.

Basic Non HTML Example: "The " & [ProductName] & " is the perfect gift for your mother. It comes in several colors " & [color1], [color2] & ", and" & [color3] & ". It also carries a " & [WarrantyLength] & " warranty!"

[Micron]

If we haven't solved the problem, try being more explicit and elaborate. In that case, I'm not understanding the issue since a memo (now called Long Text Type) can hold 65,536 characters, and I doubt the html field holds more than that for just the description. The 1024 limit applies to the criteria of a query field, so I'm not sure if you're saying the very long string is ending up in a query field as criteria or what. Also, the [bracketed] variables will get treated as parameter prompts, so that's a bit confusing to me as well. Sorry.
Maybe you should provide a real example of where you've reached or almost reached the limit. There may be better ways to accomplish the goal.

[btappan]

Here is a generic idea in much simpler terms. What I'm doing involves many more fields, and much longer chunks of text and HTML.


Product


ProductName | Color1 | Color2 | Color3 | Weight | WarrantyLength
Mixer | Red | Blue | Pink | 10lbs | 2 year
Hair Dryer | White | Orange | Black | 5lbs | 1 year


SELECT Product.ProductName AS PName, "The " & [Product].[ProductName] & " is the perfect gift for your mother. It comes in several colors " & [Product].[color1] & ", " & [Product].[color2] & ", and " & [Product].[color3] & ". It also carries a " & [Product].[WarrantyLength] & " warranty!" AS Description, [Product].[Weight] as PWeight
FROM Product


Would result in:
PName | Description | PWeight
Mixer | The Mixer is the perfect gift for your mother. It comes in several colors Red, Blue, and Pink. It also carries a 2 year warranty! | 10lbs
Hair Dryer | The Hair Dryer is the perfect gift for your mother. It comes in several colors White, Orange, and Black. It also carries a 1 year warranty! | 5lbs

[Homegrownandy]

Just as a note. When concatenating it does seem to default to short text. I had the same issue where it would cut down "long text" or "memo" fields. I never did find a solution. So if there is one id be interested also.

[Micron]

OK, so you would be limited to 1024 characters since this seems to be against one query field. One thought is to use an alias for any table name involved
SELECT ProductName AS PN FROM Product As T,... You would then write T.[color1] and not [Product].[color1]. You do create an alias but don't use it (at least in your last post).
SELECT Product.ProductName AS PName, "The " & [Product].[ProductName]
The number of characters you eliminate by using aliases for the table may not be enough, so to answer your previous question about another way to do this might be to concatenate all the fields in code, then write the full string to a table field that has been set to memo/long text type. If you're interested, here's a link to problems encountered when using memo fields. http://allenbrowne.com/ser-63.html

[btappan]

Perhaps it is not the most elagant way, but what I think I'm going to do is store the long pieces of text in another table, join them in the query by a field in my main table. This way I can just call for the field name in my expression instead of having to put all of the text in it.