VBA truncating string after 255 characters

[adhoustonj]

I have a table with 2 columns - email_group_id(integer) and email_group(long text), where email is a string of emails.
I have a saved query that selects from the table, and I open a recordset in VBA with the saved query, and assign a variable "Em" (dim as string) with the email field.

When passing through vba the long text field is truncated. Is there any solution? I tried "Dim Em as String * 1024" to increase the size, but it is still truncated.

I believe ideally the table would be email_id(integer), email_group_id(integer), and email(short text), and then I could cycle through and concat/string together my email string, but is there any solution to pass the long text through vba without the truncation?


Thanks

 

[sonic8]

It's not VBA that's truncating your data, but the query you use to retrieve the data.
Try to remove any GROUP BY or DISTINCT clauses from the query. That should solve the issue.

 

[adhoustonj]

That is very helpful! thanks. I will see if I can use a subquery to eliminate the group by, because I do have a sum operation requiring the group by.

 

[The_Doc_Man]

Note that some functions that can be used in SQL will truncate strings to 255 bytes. LONG TEXT fields are notorious for this and often require special handling. Look in the "Similar Threads" section to see if any of the articles relate to your problem.

 

[ebs17]

I have a saved query that selects from the table

You should show the SQL statement.

It could help:

SELECT Left(LongTextField, 65535) AS XY
FROM TableX
GROUP BY Left(LongTextField, 65535)

But grouping via long texts (novels) is stylistically and performance-wise a catastrophe, as is grouping via calculated fields (index use not possible).

 

[isladogs]

Following on from @sonic8's comments, see this article by Allen Browne:

Microsoft Access tips: Truncation of Memo fields

Identifies the reasons why Memo fields may be truncated after 255 characters in queries or exports in Microsoft Access.

allenbrowne.com

 

[arnelgp]

Dim Em as String * 1024

actually the above code will limit your string to 1024 chars in length.
just declare it as:

Dim Em As String

(meaning variable length).

if you are using Recordset, try to Fetch the Memo field first, then the rest of the fields, example:

select email_group, email_group_id from yourTable;

 

[adhoustonj]

Solved this by inserting the group & sum operation into a temp table, and then joining back to the email group (long text field) with the ID when opening the recordset. So I do believe it was the SQL functions mentioned in post #2 & #4. Thanks!

I didn't find too much on the VBA truncating but still assumed it was the issue. wrong... And I thought the Dim Em As String * 1024 would make sure the string could hold more than 255. The Debug.Print Len(Em) returned 255, so I was scratching my head.

Below is the top part of the procedure. q_audit_rc is a saved query.


Before:

Private Sub email_Click()
Dim db As DAO.Database
Dim rst, rst2 As DAO.Recordset
Dim strSQL, strSQL2, strSQL3, rc, emg, com, em As String
Dim ppApp As Object
Dim ppPres As Object
Dim ppCurrentSlide As Object
Dim sc, Count As Integer

Set db = CurrentDb()

'grouped NG not been emailed
strSQL = "SELECT q_audit_rc.grp_email_id, Sum(q_audit_rc.ok_ng_sub) AS Sum_ng, q_audit_rc.email_grp " _
& "From q_audit_rc " _
& "WHERE q_audit_rc.ok_ng = ""ng"" And q_audit_rc.emailed = No " _
& "GROUP BY q_audit_rc.grp_email_id, q_audit_rc.email_grp"
Set rst = db.OpenRecordset(strSQL)

After:

Private Sub email_Click()
Dim db As DAO.Database
Dim rst, rst2 As DAO.Recordset
Dim strSQL, strSQL2, strSQL3, rc, emg, com, em As String
Dim ppApp As Object
Dim ppPres As Object
Dim ppCurrentSlide As Object
Dim sc, Count As Integer

Set db = CurrentDb()

'grouped NG not been emailed
strSQL = "INSERT INTO tmp_email (grp_email_id, Sum_ng) " _
& "SELECT q_audit_rc.grp_email_id, Sum(q_audit_rc.ok_ng_sub) AS Sum_ng " _
& "From q_audit_rc " _
& "WHERE q_audit_rc.ok_ng = ""ng"" And q_audit_rc.emailed = No " _
& "GROUP BY q_audit_rc.grp_email_id "

db.Execute strSQL



strSQL = "SELECT tmp_email.grp_email_id, tmp_email.Sum_ng, eg.email_grp " _
& "From tmp_email INNER JOIN tbl_email_grp as eg on eg.grp_email_id = tmp_email.grp_email_id "

Set rst = db.OpenRecordset(strSQL)

Thanks for the help all.