Combined Fields as Memo

[Duval]

I am combining two fields in an Access database in the following query:

SELECT [standard] & ":  " & [standard text] AS combinedstandard, Standards.subject, Standards.standard
FROM Standards;

Everything works as it should with the huge exception that it's treated as a text field and thereby limited by the 255 character count so when it's displayed on a web page it gets truncated and special characters are added at the end.

How might I modify this query so that it would be treated as a memo field?

TIA/Duval

[ajetrumpet]

check this out: http://allenbrowne.com/ser-63.html

great solutions there.

[Duval]

Ajetrumpet, Thank you for the reference. It appears that the last of the group would be what I'd need to focus on but have no idea how to apply it in these circumstances.

The first SELECT in a UNION query defines the field type, so you can add another UNION ALL using a Memo field so Access gets the idea. For example, instead of:
SELECT ID, F1 & F2 AS Result FROM Table1
UNION ALL SELECT ID, F1 & F2 AS Result FROM Table2;
add a real memo field first (even though it returns no records), like this:
SELECT ID, MyMemo FROM Table3 WHERE (False)
UNION ALL SELECT ID, F1 & F2 AS Result FROM Table1
UNION ALL SELECT ID, F1 & F2 AS Result FROM Table2;

Any help would be appreciated.

[ajetrumpet]

I think what he's saying there is that if you are concating two text fields and the result is going to be longer than 255, add a memo field to your table. Leave it blank and use the UNION query to stop the truncation, like so:

SELECT TABLE.MEMO
FROM TABLE WHERE [MEMO] IS NOT NULL 

UNION ALL 

SELECT TABLE.TEXT1 & TABLE.TEXT2 
FROM TABLE

[Duval]

I'm just lost, sorry. Not too experienced with queries. Been digging around there and tried the link but I'm getting lost there too as I'm not seeing the commands that are referenced.

http://support.microsoft.com/kb/207668

[ajetrumpet]

I'm just lost, sorry. Not too experienced with queries. Been digging around there and tried the link but I'm getting lost there too as I'm not seeing the commands that are referenced.

http://support.microsoft.com/kb/207668

That article is saying the same thing as Allen Browne's I think.

If you want to upload your file Duval, I'd be happy to do it for you. It's a complex concept I know, but let us remember that it doesn't have to be this way if MS was interested at all in creating purposeful software. =)

Sometimes it makes me feel better knowing that.

[Duval]

That would really be appreciated and maybe I can pick up something by looking at the sql. http://www.ecologyclub.org/lessonplans.mdb

[ajetrumpet]

here you go.

notice the use of the memo field as many times and the number of fields you actually want. I think that's necessary. I put the LEN field in there to just to prove that it's not truncating at 255.

HTH

[Duval]

Thanks a ton, downloading now.

[Duval]

Playing with it now, still getting the truncated text when using combined standard but I'm sure I'll get it. Thank you so much for your time!

[ajetrumpet]

Playing with it now, still getting the truncated text when using combined standard but I'm sure I'll get it. Thank you so much for your time!

I saw a field there with 257 characters, so I know it works. (Hopefully!) Good luck to you.

[Duval]

Right you are and thanks again... definitely above and beyond!