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
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