MS访问VBA字符串连接自动添加回车

Question

使用MS Access 2016，我将在查询设计器中创建的查询复制到VBA，以便根据表单上的某些用户选择动态更改WHERE子句。然后将此查询设置为报表的记录源。

在表单上，用户可以选择配置三项: 1)日期，2)系统(所有系统或特定系统)，3)子系统(所有子系统或特定子系统)。在VBA中，我使用字符串连接组装查询语句，并根据所选选项设置WHERE子句。

报告失败了，所以我开始调查。当我将字符串打印到“立即”窗口(debug.print)时，我可以很快看出哪里出了问题。字符串在大约1138个字符(根据所选选项提供或接受)时会变得相当长。围绕字符1027，VBA似乎自动插入一个回车。

这是正常的吗？有办法绕道吗？

这是我的代码：

'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

这是打印的结果(为运输返回的可见性添加了行提要)：

选择Reliability_MotorData.DateStamp，Config_BaseData_Motors.Service，Config_BaseData_Motors.SysCapacity_Pct作为总容量，Reliability_MotorMasterList.EquipmentName作为设备，Config_BaseData_Motors.EquipSystem，Config_BaseData_Motors.EquipSubSystem，Config_BaseData_Motors.Service作为系统，Motor_GetSubSystemName(Config_BaseData_Motors.EquipSubSystem)作为子系统，IIf(Motor_GetServiceStatus(Reliability_MotorData.MotorID，SelectDate=-1，"OOS"，IIf(Motor_GetServiceStatus(Reliability_MotorData.MotorID，SelectDate=0，“服务中”，(“未知”)作为服务模式，Motor_GetCurrentMotorCapacity(Reliability_MotorData.MotorID，SelectDate)作为当前容量(Config_BaseData_Motors右加入Reliability_MotorMasterList ON Config_BaseData_Motors.MotorID = Reliability_MotorMasterList.MotorID)内部连接Reliability_MotorData ON Reliability_MotorMasterList.MotorID = Reliability_MotorData.MotorID，其中(Reliability_MotorData.DateStamp= #8/10/2016# ) 和((Config_BaseData_Motors.EquipSystem类似"")和((Config_BaseData_Motors.EquipSubSystem类似“))；

我可以稍微缩短我的查询，并使它在这个实例中工作，但是如果我想让他们选择多个系统或子系统呢？最后，我将再次开始使用所有字符，并遇到同样的问题。

是否有更好的方法来实现我的目标使用连接？我尝试使用一条大型级联语句，并为WHERE子句使用第二个字符串变量，然后将它们一起打印出来，debug.print ssql & ssql2。它们都产生了相同的结果。

或者，如果有一种方法可以动态编辑MS Access查询以更改where子句；这可能是一个选项。

谢谢

Answer 1

无论如何，最佳实践是不要在代码中构建SQL查询。只需保存所构建的查询，然后使用参数化querydef调用它。例如保存查询：

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]));

并查询如下：

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