Long text query results.

[Homegrownandy]

I have a two querys which put together some data for a "job". I'm trying to put all of this data onto emails and most of this is set up. The last problem I seem to have is when using concatenation the text string becomes short text. So in long entries data can be lost (not visible to who we send the job too).

Query 1:


this does some formatting for each entry.

Note: [notes] & "
 " & [Date_Added] & "
"

Query 2:
this combines each note for a job. Making it one text string to insert into the email.

Notes: ConcatRelated("Note","z_concatrelated_test","Job_ID = " & [job_ID])

The "notes" field is in a notes table linked to the jobs table. This is a long text field. The date_added field is associated with each note and is added automatically when an entry is made. This is a date/time field.

If it's not possible to make the result long text, I would appreciate alternative suggestions.

Thanks!

I want this data to be put into excel where I will use some other code I have to make the apointments. Its possible to merge the related data in excel or use VBA to achieve what i need. I'm unsure whats the best to do now.

[ranman256]

I assume NOTES is a memo field. Queries can cut the memo field to 255 chars. (thanks Microsoft)
Google solutions for this. (not many)

[Homegrownandy]

Its a long text field. But yes, same problem. Do you think its a better idea to combine these in excel with VBA?

Im attempting to use the union all query to resolve this. Ill see how that goes.

[Homegrownandy]

Trying to use this or something similar. But i keep getting errors:

SELECT Field1
FROM z_concatrelated_test, z_concatrelated_test2, z_test;
UNION ALL,
SELECT note & Job_ID AS Field1 FROM z_concatrelated_test2;

The z_test table is empty but ive read to use something like this for formatting purposes.

Anything obviously wrong here? (syntax/invalid sql statement errors when changing.)

[ranman256]

I had good luck with appending to a table (it kept all the text.)
then an update query to change things.
Append qry and export from a table kept the text, but other queries cut it off.

[Homegrownandy]

Thanks for the reply ill look into it.

[Homegrownandy]

So you append the old data to the new data? then update the old data?

Is that correct?

Or did you append the date and the text then the update addes to the old data?

Either is fine but im not sure if an update can add or only change data.

[Homegrownandy]

Ignore that last one. But i still have some concerns.

I need to append data twice. I need to add the date to the end of the text string in one (this is done).

I'll then need to append that new text string with the date at the end to the existing data.

I know how to do the appends/updates on their own and with my direction. But how would I set this up to work on a form?

What would be the link between the data? Because If i use the Job_ID for example it will append old data in that table. I'm just a bit confused.

Thanks.