Exported query truncating string (1 Viewer)

[BlueJacket]

I created a user-defined function to create a string in a query to show a list of defendants in a single field. I'm then exporting the query to a text file to use for a word merge. The problem is that the string (strDefList) can be quite long and gets truncated when it gets exported. The string appears fine in the query itself.

What are some ways I can tackle this?

Thanks in advance.

 

[Ranman256]

exporting from queries can only export 255 chars.
exporting a table using a memo field should export all characters >255

 

[BlueJacket]

Ok, so I can use an INSERT INTO query for the table, which I can then export to a text file and use for the word merge.

 

[The_Doc_Man]

The other possibility - if this is a MAILMERGE - is to let Word do the import from a query that includes the memo field. (Now called "Long Text" in newer versions of Access.)

 

[isladogs]

As Doc Man just stated, you don't need to use the intermediary text file at all
Adding an unnecessary step just provides more things to go wrong

 

[BlueJacket]

So what would that look like? I'm still trying to wrap my head around all the new vba code I'm learning about that goes into to automating the word merge/mailmerge process.

It sounds like I need code to 1) run a query that will append/insert values into a table (should this table already be made that I just clear out after every use or make a whole new table and delete it every time?). 2) run a query based off the table with a memo field that Word can import from.

 

[BlueJacket]

This is the code I was using for a different mail merge:

Private Sub butCreateSEoS_Click()

'Code source credit to: http://stackoverflow.com/questions/3905580/mail-merge-started-by-vba-in-access-let-word-open-database-again

        Dim pathMergeTemplate As String
        Dim strSQL As String

        pathMergeTemplate = "C:\...\...\Access\"

        strSQL = "SELECT * FROM mqrySPSheriffEntryofService"

        Dim qd As DAO.QueryDef
        
        Set qd = New DAO.QueryDef
        qd.sql = strSQL
        qd.Name = "mmexport"

        CurrentDb.QueryDefs.Append qd


        DoCmd.TransferText _
                  acExportDelim, , _
                  "mmexport", _
                  pathMergeTemplate & "qryMailMerge.txt", _
                  True

        CurrentDb.QueryDefs.Delete "mmexport"

        qd.Close
        Set qd = Nothing

        Dim appWord As Object
        Dim docWord As Object

        Set appWord = CreateObject("Word.Application")

        appWord.Application.Visible = True

        Set docWord = appWord.Documents.Add(Template:=pathMergeTemplate & "Template Form.docx")


        docWord.MailMerge.OpenDataSource Name:=pathMergeTemplate & "qryMailMerge.txt", LinkToSource:=False

        Set docWord = Nothing

        Set appWord = Nothing

End Sub

You're saying qryMailMerge.txt is unnecessary here?

 

[isladogs]

It sounds like I need code to 1) run a query that will append/insert values into a table (should this table already be made that I just clear out after every use or make a whole new table and delete it every time?). 2) run a query based off the table with a memo field that Word can import from.

1. No - just use the existing table - forget all these queries!
2. In Word, just use the table as your merge data source

That's it!

 

[Uncle Gizmo]

Thank you blue jacket for suggesting that I could do a set of videos on "Word merges" in my thread "Problem in MS Access? - Want to See a YouTube Video Solving it?"

To keep that thread clean, I thought it advisable to post in this tread, my thoughts on Word.

I don't do much coding specifically with "Word merges". I would have to do a bit of research to be able to develop reasonable videos, and the videos would suffer from the fact that I don't have much experience in Word specifically.

My first port of call for any research on Word would be Helen Feddema's excellent website here

http://www.helenfeddema.com/Code Samples.htm

I thought it might be of interest.

 

[isladogs]

I'd also suggest looking at Albert kallal's easy mail merge feature where all the merge code is done from access before opening word. Its very adaptable and will save you a lot of coding time

http://kallal.ca/wordmerge/index.html

 

[BlueJacket]

Just to put this into context, the reason why I went the exported text file way was because I was having problems with using Mail Merge based off a form with parameters on a split database. Exporting the query to a text file resolved the issue, though I suspect using a table might also work around that particular problem.

Also, thanks for the resources.