Solved Can't find what's trimming my string to 255 characters (1 Viewer)

561414

Active member
Local time
Today, 09:10
Joined
May 28, 2021
Messages
280
Hello everyone.

I wrote this function which fills MSWord templates, like the mail merge function, but customized to my needs. It basically extracts a query definition's SQL string, it concatenates the where clause and, using the first record, it finds the field names of the SQL string in the docx template, the placeholders in the template use this format {{fieldName}}. It then generates a file in the temporary folder of Windows and returns the path.

Code:
Function templateReplacer( _
                    querydef_name As String, _
                    where_clauses As String, _
                    template_path As String, _
                    desired_filename As String) _
                    As String

    On Error GoTo errHandler
    Dim sql1 As String
    sql1 = CurrentDb.QueryDefs(querydef_name).SQL

    Dim sql2 As String
    sql2 = Replace(sql1, ";", " WHERE " & where_clauses)
'    Debug.Print sql2
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(sql2)



    Dim oWord As Object
    Set oWord = CreateObject("Word.Application")

    Dim oDoc As Object
    Set oDoc = oWord.Documents.Open(template_path)


'   |------------------- REPLACE {{FIELDS}} -------------------|
    Dim field As Variant

    Dim emptyFields As String
    emptyFields = ""

    Dim oStory As Object
    For Each field In rs.Fields
'        Debug.Print field.Name, field.Value
        For Each oStory In oDoc.StoryRanges
            Do
                With oStory.Find
                    If Len(field) > 255 Then
                        ClipBoard_SetData field.Value
                        .Execute FindText:="{{" & field.Name & "}}", ReplaceWith:="^c", _
                        Format:=True, Replace:=2
                    Else
                        .Execute FindText:="{{" & field.Name & "}}", ReplaceWith:=Nz(field, ""), _
                        Format:=True, Replace:=2
                    End If
                End With
            Set oStory = oStory.Next
            Loop Until oStory Is Nothing
        Next oStory
        If IsNull(field) Then
            emptyFields = emptyFields & vbCr & field.Name
        End If
    Next field

'   |------------------- FILE NAMING AND SAVING -------------------|

    Dim tempFolder As String
    tempFolder = Environ("Temp")

    Dim outputFullPath As String
    outputFullPath = tempFolder & "\" & desired_filename & Format(Date, "ddmmyy") & Format(Time, "hhmmss") & ".docx"

    oDoc.SaveAs2 outputFullPath

    Set oWord = Nothing
    Set oDoc = Nothing

    If Len(emptyFields) > 0 Then
        MsgBox "Empty fields: " & vbCr & emptyFields
    End If

    templateReplacer = outputFullPath

errHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
    End If
    
    Set oWord = Nothing
    Set oDoc = Nothing

End Function

There's something trimming my strings to 255 characters. As you can see in the code, when the field is longer than 255 characters, it sends the contents of the field to the clipboard and then pastes it, instead of just using the field.value property, which would trim the text to 255 characters. That behaviour worked great until I decided to use a function to add 'minus' symbols to keep filling the blanks when the text ends.

Sounds weird, maybe? I'll try to explain further: Imagine a template with 4 blank lines that you have to fill, but your text is only half a line long, how do you fill the rest? well I chose the path of doing it with the Access Query Builder like this:
Suppose I need to fill 500 characters:
IIf(Len[field]<500,String(500-Len([field]), "-"),"")
But if those 'minus' symbols exceed the length of 255, they get trimmed, sometimes it outputs n?n or ?;? at the end.

Please check the attached sample database. I suspect the problem is actually in the clipboard code, which is this:
Code:
Option Compare Database
Option Explicit
#If VBA7 Then
  Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
    ByVal dwBytes As LongPtr) As LongPtr
  Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
  Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As LongPtr
  Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
    ByVal hMem As LongPtr) As LongPtr
#Else
  Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
    ByVal dwBytes As Long) As Long
  Private Declare Function CloseClipboard Lib "user32" () As Long
  Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
  Private Declare Function EmptyClipboard Lib "user32" () As Long
  Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
    As Long, ByVal hMem As Long) As Long
#End If

Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
'PURPOSE: API function to copy text to clipboard
'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

#If VBA7 Then
  Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr
  Dim hClipMemory As LongPtr, x As LongPtr
#Else
  Dim hGlobalMemory As Long, lpGlobalMemory As Long
  Dim hClipMemory As Long, x As Long
#End If

'Allocate moveable global memory
  hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

'Lock the block to get a far pointer to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

'Unlock the memory.
  If GlobalUnlock(hGlobalMemory) <> 0 Then
    MsgBox "Could not unlock memory location. Copy aborted."
    GoTo OutOfHere2
  End If

'Open the Clipboard to copy data to.
  If OpenClipboard(0&) = 0 Then
    MsgBox "Could not open the Clipboard. Copy aborted."
    Exit Function
  End If

'Clear the Clipboard.
  x = EmptyClipboard()

'Copy the data to the Clipboard.
  hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:
  If CloseClipboard() = 0 Then
    MsgBox "Could not close Clipboard."
  End If

End Function

I suspect that because I wrote a custom function to not use the built-in String function.
Code:
Public Function StringX(amt As Long, str As String) As String
  Dim finalString As String
  Dim i As Long
  For i = 1 To amt
    finalString = finalString & str
  Next
  StringX = finalString
End Function
But it returns the same.

What could it be?
 

Attachments

  • MyTemplateFiller.zip
    68 KB · Views: 148

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:10
Joined
Feb 28, 2001
Messages
26,999
One possibility is the recordset near your banner "REPLACE {{FIELDS}}" because if any of the fields are of type Short Text then you hit a 255 byte limit right there. And you DO use Field.Value in that area. You say "if the field is more than 255 characters" - but for a DAO recordset, unless the field is of type Long Text, it can't be.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:10
Joined
May 7, 2009
Messages
19,169
no comment on your code but you can do the same using Report in Access.
 

561414

Active member
Local time
Today, 09:10
Joined
May 28, 2021
Messages
280
...unless the field is of type Long Text...
Oh thanks. That's probably the issue. But how does one specify the type of data that should be used? Using the sample database, this is the SQL statement:
SQL:
SELECT p.PeopleId, p.TheName, p.TheLastName, p.SomeOtherField, [o].TheObservation, IIf(Len([TheObservation])<910,String(910-Len([TheObservation]),"-"),"") AS FillTheRest
FROM People AS p INNER JOIN Observations AS o ON p.PeopleId = [o].PeopleId;

FillTheRest is the name of the expression that builds the 'minus' symbol strings and it does not belong to a table definition where I could choose its data type.

EDIT:
I can confirm that FillTheRest is being treated as short text via debug.print.

no comment on your code but you can do the same using Report in Access.
Thanks, arnelgp. I figured it would take me several weeks to set that up using Access Reports. I'm talking about 160 different templates right now, and it will grow to nearly a thousand within a month. To me, it looked simpler to just give the users the field names so they could set up their templates themselves.
 
Last edited:

SHANEMAC51

Active member
Local time
Today, 18:10
Joined
Jan 28, 2022
Messages
310
Sounds weird, maybe? I'll try to explain further: Imagine a template with 4 blank lines that you have to fill, but your text is only half a line long, how do you fill the rest? well I chose the path of doing it with the Access Query Builder like this:
Suppose I need to fill 500 characters:
I'm not sure that you will be satisfied with such a result
, the alignment is left and wide
 

Attachments

  • Screenshot_8.png
    Screenshot_8.png
    60.3 KB · Views: 131
  • Screenshot_9.png
    Screenshot_9.png
    62.6 KB · Views: 141

SHANEMAC51

Active member
Local time
Today, 18:10
Joined
Jan 28, 2022
Messages
310
slightly corrected, page-width alignment
* * and mmm for debugging
 

Attachments

  • Screenshot_12a.png
    Screenshot_12a.png
    59.7 KB · Views: 145

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:10
Joined
Feb 28, 2001
Messages
26,999
But how does one specify the type of data that should be used?

The data type cannot be specified by SQL, but in the table's field definitions, this should be easy. However, ...

FillTheRest is the name of the expression that builds the 'minus' symbol strings and it does not belong to a table definition where I could choose its data type.

Now we have a problem. If something is an expression that doesn't belong to a table, its default type is defined from analysis of the data types that feed it as modified by how it is used. (Which is how numeric expressions promote from integer to floating-point, for example.) Unfortunately, there is neither a type-mark hint (e.g. $ for string, # for double, % for long, etc) nor a way to specify that data type in-line because data type declarations are done in DDL, not SQL, and they can't co-exist in the same statement. That expression doesn't exist if the query isn't open so there is nothing to specify or modify.
 

561414

Active member
Local time
Today, 09:10
Joined
May 28, 2021
Messages
280
slightly corrected, page-width alignment
* * and mmm for debugging
Thanks, SHANEMAC51, I guess I have to worry more now that I see I also have to take into account the alignment of the text. I thought a monospace font would take care of that but I see I was wrong. By the way, if that's not what you are demonstrating with the screenshots then I'm lost.

Unfortunately, there is neither a type-mark hint (e.g. $ for string, # for double, % for long, etc) nor a way to specify that data type in-line because data type declarations are done in DDL, not SQL, and they can't co-exist in the same statement. That expression doesn't exist if the query isn't open so there is nothing to specify or modify.
Thank you very much, The_Doc_Man, you found what was trimming the string. I'm not sure if I should mark this as solved, start another thread or wait, I'm gonna see if I can push the minus symbols to the long text field somehow for now.

EDIT: marked as solved. I haven't found a solution but the reason for the trimming was found.

Thanks everyone.
 
Last edited:

Users who are viewing this thread

Top Bottom