MS Access VBA string concatenation automatically adds carriage return

Question

Using MS Access 2016, I am copying a query I created in the query designer to VBA so I can dynamically change the WHERE clause based on some user selections on a form. This query is then set to the record source of a report.

On the form, the user has the option to configure three items: 1) the date, 2) System (all systems or a specific one), 3) a subsystem (all subsystems or a specific one). In VBA, I assemble the query statement using string concatenation and set up the WHERE clause based on the options selected.

The report was failing, so I started investigating. When I print out the string to the immediate window (debug.print) I could see what was wrong pretty quickly. The string gets rather lengthy at approximately 1138 characters (give or take based on the options selected). Around character 1027 VBA seems to automatically insert a carriage return.

Is this normal? Is there a way around this?

Here is my code:

'Procedure to execute report
Private Sub cmdExecReport_Click()
    On Error GoTo ErrHandler

    Dim ssql As String
    Dim ssql2 As String
    Dim StartDate As Date
    Dim System As Long
    Dim SubSystem As Long


    'Step 1: Acquire data from form

    'Acquire start date
    StartDate = Me.txtReportDate_Start.Value
    'Acquire System
    System = Me.cboSystem.Value
    'Acquire SubSystem
    SubSystem = Me.cboSubSystem.Value


    'Step 2: Configure record source

    'Assemble the record source string based on the selected items
    ssql = "SELECT Reliability_MotorData.DateStamp, Config_BaseData_Motors.Service, Config_BaseData_Motors.SysCapacity_Pct AS [Total Capacity], "
    ssql = ssql & "Reliability_MotorMasterList.EquipmentName AS Equipment, Config_BaseData_Motors.EquipSystem, Config_BaseData_Motors.EquipSubSystem, "
    ssql = ssql & "Motor_GetSystemName([Config_BaseData_Motors].[EquipSystem]) AS System, Motor_GetSubSystemName([Config_BaseData_Motors].[EquipSubSystem]) AS [Sub System], "
    ssql = ssql & "IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID],[SelectDate])=-1," & """" & "OOS" & """" & ",IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID],"
    ssql = ssql & "[SelectDate])=0," & """" & "In Service" & """" & "," & """" & "Unknown" & """" & ")) AS [Service Mode], Motor_GetCurrentMotorCapacity([Reliability_MotorData].[MotorID],[SelectDate]) AS [Current Capacity] "
    ssql = ssql & "FROM (Config_BaseData_Motors RIGHT JOIN Reliability_MotorMasterList ON Config_BaseData_Motors.MotorID = Reliability_MotorMasterList.MotorID) "
    ssql = ssql & "INNER JOIN Reliability_MotorData ON Reliability_MotorMasterList.MotorID = Reliability_MotorData.MotorID "
    
    'Configure the Where clause
    ssql = ssql & "WHERE ((Reliability_MotorData.DateStamp = #" & StartDate & "#) "
    
    'Configure the System list
    ssql = ssql & "AND ((Config_BaseData_Motors.EquipSystem "
    'Check the system selected
    If System = -1 Then 'all systems
        ssql = ssql & "Like " & """" & "*" & """" & ") "
    Else    'specific one
        ssql = ssql & "= " & System
    End If
    'Add closing paren
    ssql = ssql & ") "


    'Configure the SubSystem list
    ssql = ssql & "AND ((Config_BaseData_Motors.EquipSubSystem "
    'Check the subsystem selected
    If SubSystem = -1 Then  'all subsystems
        ssql = ssql & " Like " & """" & "*" & """" & ")"
    Else    'specific one
        ssql = ssql & "= " & SubSystem
    End If

    'Add closing paren & ;
    ssql = ssql & ");"

    Debug.Print ssql

    'Step 3: Launch Report
   ' DoCmd.OpenReport "Motor Capacity 4", acViewPreview, , , , ssql

    Exit Sub

ErrHandler:
    'Write to event log
    Call WriteWinEventLog(Error, Now() & " - " & "Execution error on form " & CurrentFormName & " in routine cmdExecReport_Click" & vbCrLf _
    & "Error Number: " & Err.Number & vbCrLf & "Source: " & Err.Source & vbCrLf & "Description: " & Err.Description)

End Sub

Here is the result of the print (line feed added for visibility of carriage return):

SELECT Reliability_MotorData.DateStamp, Config_BaseData_Motors.Service, Config_BaseData_Motors.SysCapacity_Pct AS [Total Capacity], Reliability_MotorMasterList.EquipmentName AS Equipment, Config_BaseData_Motors.EquipSystem, Config_BaseData_Motors.EquipSubSystem, Motor_GetSystemName([Config_BaseData_Motors].[EquipSystem]) AS System, Motor_GetSubSystemName([Config_BaseData_Motors].[EquipSubSystem]) AS [Sub System], IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID],[SelectDate])=-1,"OOS",IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID],[SelectDate])=0,"In Service","Unknown")) AS [Service Mode], Motor_GetCurrentMotorCapacity([Reliability_MotorData].[MotorID],[SelectDate]) AS [Current Capacity] FROM (Config_BaseData_Motors RIGHT JOIN Reliability_MotorMasterList ON Config_BaseData_Motors.MotorID = Reliability_MotorMasterList.MotorID) INNER JOIN Reliability_MotorData ON Reliability_MotorMasterList.MotorID = Reliability_MotorData.MotorID WHERE ((Reliability_MotorData.DateStamp = #8/10/2016# )

AND ((Config_BaseData_Motors.EquipSystem Like "") ) AND ((Config_BaseData_Motors.EquipSubSystem Like ""));

I can shorten my query a bit and make it work in this instance, but what if I want to allow them to select more than one system or subsystem? Eventually I'll start using up characters again and run into the same issue.

Is there a better way to achieve my goal using concatenation? I have tried using one large concatenation statement and using a second string variable for the WHERE clause and then printing them out together debug.print ssql & ssql2. They all produce the same result.

Alternatively, if there way a way to dynamically edit a MS Access query to change the where clause; that could be an option.

Thank you

Have you tried using aliases, sth. like this SELECT fieldname AS f FROM tablename t WHERE t.f = 1. This would be an easy way to shorten your query. Another way would be to save the query in a view and use the where clause on that, i.e. selelct * from view where... — Mister 832
you can change the query definition using Currentdb.QueryDefs("query_name").SQL = "new SQL" — winghei

Mikegrann Mikegrann · Accepted Answer · 2016-08-11T15:47:22

Best practice is not to build SQL queries within code, anyway. Just save the query you built, then use a parameterized querydef to call it. E.g. save a query:

SELECT Reliability_MotorData.DateStamp,
   Config_BaseData_Motors.Service,
   Config_BaseData_Motors.SysCapacity_Pct AS [Total Capacity],
   Reliability_MotorMasterList.EquipmentName AS Equipment, 
   Config_BaseData_Motors.EquipSystem, 
   Config_BaseData_Motors.EquipSubSystem, 
   Motor_GetSystemName([Config_BaseData_Motors].[EquipSystem]) AS System, 
   Motor_GetSubSystemName([Config_BaseData_Motors].[EquipSubSystem]) AS [Sub System], 
   IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID], [SelectDate])=-1,
      "OOS",
      IIf(Motor_GetServiceStatus([Reliability_MotorData].[MotorID],[SelectDate])=0,
         "In Service",
         "Unknown")) AS [Service Mode], 
   Motor_GetCurrentMotorCapacity([Reliability_MotorData].[MotorID],[SelectDate]) AS [Current Capacity]
FROM (Config_BaseData_Motors 
   RIGHT JOIN Reliability_MotorMasterList 
      ON Config_BaseData_Motors.MotorID = Reliability_MotorMasterList.MotorID)
   INNER JOIN Reliability_MotorData 
      ON Reliability_MotorMasterList.MotorID = Reliability_MotorData.MotorID
WHERE ((Reliability_MotorData.DateStamp = [SearchDate])
   AND ((Config_BaseData_Motors.EquipSystem Like [SearchSystem])) 
   AND ((Config_BaseData_Motors.EquipSubSystem Like [SearchSubSystem]));

And query it like:

Private Sub cmdExecReport_Click()
    On Error GoTo ErrHandler

    Dim qdf As QueryDef
    Dim StartDate As Date
    Dim System As Long
    Dim SubSystem As Long

    'Step 1: Acquire data from form
    StartDate = Me.txtReportDate_Start.Value 'Acquire start date
    System = Me.cboSystem.Value 'Acquire System
    SubSystem = Me.cboSubSystem.Value 'Acquire SubSystem

    'Step 2: Acquire QueryDef
    Set qdf = CurrentDB.QueryDefs("qryMyParameterQuery") ' EDIT THIS

    'Step 3: Substitute Parameters
    'Substitute date
    qdf.Parameters("SearchDate") = "#" & StartDate & "#"

    'Substitute system
    If System = -1 Then
        qdf.Parameters("SearchSystem") = """*"""
    Else
        qdf.Parameters("SearchSystem") = """" & System & """" ' format as String
    End If

    'Substitute subsystem
    If SubSystem = -1 Then
        qdf.Parameters("SearchSubSystem") = """*"""
    Else
        qdf.Parameters("SearchSubSystem") = """" & SubSystem & """" ' format as String
    End If

    ' Step 4: Open report, recordset, etc.
    ' Example:
    'Dim rst As Recordset
    'Set rst = qdf.OpenRecordset()
    '...

End Sub

MS Access VBA string concatenation automatically adds carriage return

1 Answers