none
Trim function for report data ? RRS feed

  • Question

  • I'm coding a report and it is working fine but is printing out a table column with some bad data in it. Several records have leading spaces in the data, they come out at the top of the report and look bad.

    I've tried the TRIM function in the SQL and in the control source but Access does not like either.

    How can I apply the TRIM function as this column is read into the report?

    Thanks.

    Friday, November 6, 2015 12:46 AM

Answers

  • I've tried the TRIM function in the SQL and in the control source but Access does not like either.

    How can I apply the TRIM function as this column is read into the report?

    Access does not like either? What do you mean? If you use trim in your SQL, you will create an alias for the column name. You then just refer to the alias in your report.  If you have white space other than just spaces in your text, then use the procedure I provided for you: TrimWS.

    Changing the report's record source SQL:

    SELECT Trim([TextField]) AS TrmTextField FROM TableName;

    txtTextField>Property>Control Source: TrmTextField

    OR

    Changing the control's control source:

    txtTextField>Property>Control Source: =Trim(TextField)

    Monday, November 9, 2015 8:44 PM

All replies

  • I've tried the TRIM function in the SQL and in the control source but Access does not like either.

    How can I apply the TRIM function as this column is read into the report?

    Thanks.

    Rather than apply trim anywhere, try cleansing your data with an update query and the trim function.  Give the following procedure a try.  It will trim all white space from your string.

    Option Compare Database
    Option Explicit
    
    Public Const gcALL = 0
    Public Const gcLEFT = 1
    Public Const gcRIGHT = 2
    
    Function TrimWS(StringValue As Variant, Optional TrimOption As Integer = gcALL) As Variant
        'Version:   1.0
        'Created:   01/01/1995 06:32    By: RM
        'Purpose:   Trim white space from string
        
        On Error GoTo Err_Process
        
        Dim strChar As String
        Dim varReturn As Variant
        Dim blnLoop As Boolean
        Dim lngCount As Long
        Dim strBuf As String
        Dim strMsg As String
        
        varReturn = Null
        
        If (Not IsNull(StringValue)) Then
            strBuf = CStr(StringValue)
            If (Len(strBuf) > 0) Then
                Select Case TrimOption
                Case gcLEFT
                    Do
                        blnLoop = False
                        If (Len(strBuf) > 0) Then
                            strChar = Mid(strBuf, 1, 1)
                            If (IsSpace(strChar)) Then
                                strBuf = Right(strBuf, Len(strBuf) - 1)
                                blnLoop = True
                            End If
                        End If
                    Loop While blnLoop
                Case gcRIGHT
                    Do
                        blnLoop = False
                        If (Len(strBuf) > 0) Then
                            strChar = Mid(strBuf, Len(strBuf), 1)
                            If (IsSpace(strChar)) Then
                                strBuf = Left(strBuf, Len(strBuf) - 1)
                                blnLoop = True
                            End If
                        End If
                    Loop While blnLoop
                Case gcALL
                    Do
                        blnLoop = False
                        If (Len(strBuf) > 0) Then
                            strChar = Mid(strBuf, 1, 1)
                            If (IsSpace(strChar)) Then
                                strBuf = Right(strBuf, Len(strBuf) - 1)
                                blnLoop = True
                            End If
                        End If
                    Loop While blnLoop
                    Do
                        blnLoop = False
                        If (Len(strBuf) > 0) Then
                            strChar = Mid(strBuf, Len(strBuf), 1)
                            If (IsSpace(strChar)) Then
                                strBuf = Left(strBuf, Len(strBuf) - 1)
                                blnLoop = True
                            End If
                        End If
                    Loop While blnLoop
                End Select
            End If
            varReturn = strBuf
        End If
        
    Exit_Process:
        TrimWS = varReturn
        Exit Function
    
    Err_Process:
        Select Case Err
        Case Else
            strMsg = "An unexpected error occurred in procedure: TrimWS" & vbCrLf & vbCrLf
            strMsg = strMsg & Err.Number & " " & Err.Description
            MsgBox strMsg, vbExclamation, "Error"
        End Select
    
        Resume Exit_Process
    
    End Function
    
    Function IsSpace(Character As Variant) As Boolean
        'Version:   1.0
        'Created:   01/01/1995 05:42    By: RM
        
        On Error GoTo Err_Process
        
        Dim blnReturn As Boolean
        Dim strChars As String
        Dim strMsg As String
        
        blnReturn = False
        
        If (Not IsNull(Character)) Then
            'Carriage Return, Line Feed, Space, HTML nbsp
            strChars = vbCr & vbLf & vbTab & " " & Chr(160) & vbNullChar
            blnReturn = (InStr(1, strChars, Character) > 0)
        End If
    
    Exit_Process:
        IsSpace = blnReturn
        Exit Function
    
    Err_Process:
        Select Case Err
        Case Else
            strMsg = "An unexpected error occurred in procedure: IsSpace" & vbCrLf & vbCrLf
            strMsg = strMsg & Err.Number & " " & Err.Description
            MsgBox strMsg, vbExclamation, "Error"
        End Select
    
        Resume Exit_Process
    
    End Function
    

    Friday, November 6, 2015 1:13 AM
  • There are 3 trim functions: LTrim, RTrim, and Trim. They all work. Inserting a function into a query usually requires some kind of alias expression, like RealFName: Trim(FirstName).


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, November 6, 2015 1:56 AM
  • The difference between Trim for VBA and the TrimWS procedure provided here is that TrimWS will trim out all white space to include Carriage Return, Line Feed, Space, Tab, and HTML nbsp.

    The reason I suggest cleansing your data vs. trying to alter the display with functions like trim is that such functions hinder performance.  It would be better to clean up data that is known to be a problem.  That's just good database administration.


    Friday, November 6, 2015 5:18 AM
  • I'm not allowed to change the data in the table. I want only to change the data as it is printed to the report.
    Monday, November 9, 2015 8:15 PM
  • I've tried the TRIM function in the SQL and in the control source but Access does not like either.

    How can I apply the TRIM function as this column is read into the report?

    Access does not like either? What do you mean? If you use trim in your SQL, you will create an alias for the column name. You then just refer to the alias in your report.  If you have white space other than just spaces in your text, then use the procedure I provided for you: TrimWS.

    Changing the report's record source SQL:

    SELECT Trim([TextField]) AS TrmTextField FROM TableName;

    txtTextField>Property>Control Source: TrmTextField

    OR

    Changing the control's control source:

    txtTextField>Property>Control Source: =Trim(TextField)

    Monday, November 9, 2015 8:44 PM