在VBA中使用包含单引号数据的过滤器(Applying Filter in VBA with data including Single Quotes)

我一直试图让一个简单的过滤器在一个表单上工作，在这个表单上应用基于Access DB中两列的过滤器。

Private Sub cmb_Name_AfterUpdate()  
Me.cmb_WorkCity.Requery  
DoCmd.ApplyFilter , "[Employee Name]='" & Me.cmb_Name.Column(0) & "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"  
End Sub  
 
 在当前测试数据中有两个实例，其中员工姓名具有单引号，例如O'Malley。 这会导致查询中出现“语法错误（缺少运算符）” 
 
 有关如何解决此问题的任何想法？ 
I've been trying to get a simple filter to work on a form where it applies filters based on two columns in my Access DB. 
 
Private Sub cmb_Name_AfterUpdate()  
Me.cmb_WorkCity.Requery  
DoCmd.ApplyFilter , "[Employee Name]='" & Me.cmb_Name.Column(0) & "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"  
End Sub  
 
There are two instances in the current test data where the Employee Name has a single quote e.g. O'Malley for instance. This results in a 'Syntax error (missing operator) in query 
 
Any thoughts on how to resolve this issue?
                
最满意答案
                
                    
                        
 使用Replace()替换员工姓名中每个单引号的两个单引号。 
 
Private Sub cmb_Name_AfterUpdate()
Dim strFilter As String
Me.cmb_WorkCity.Requery
'DoCmd.ApplyFilter , "[Employee Name]='" & Me.cmb_Name.Column(0) & "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"
strFilter = "[Employee Name]='" & Replace(Me.cmb_Name.Column(0), "'", "''") & _
    "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"
Debug.Print strFilter ' <- view in Immediate window; Ctrl+g will take you there
DoCmd.ApplyFilter , strFilter
End Sub
Use Replace() to substitute two single quotes for each one single quote in employee name.
 
Private Sub cmb_Name_AfterUpdate()
Dim strFilter As String
Me.cmb_WorkCity.Requery
'DoCmd.ApplyFilter , "[Employee Name]='" & Me.cmb_Name.Column(0) & "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"
strFilter = "[Employee Name]='" & Replace(Me.cmb_Name.Column(0), "'", "''") & _
    "' And [Movement Type]='" & Me.cmb_Name.Column(1) & "'"
Debug.Print strFilter ' <- view in Immediate window; Ctrl+g will take you there
DoCmd.ApplyFilter , strFilter
End Sub