Special Characters in Data

[ismailr]

Hi,

Please guide me.
I have a table tblDepartment with below records;

DeptID Department
1001 Finance
1002 Mr.Bean's Office
1003 CEO's Office
1004 Bazza@Home
1005 HR

On a form a field is available as txtDepartment. Once I enter value in txtDepartment a query should show the DeptID. For this purpose I am using the below script;

Dim sqltxt As String
Dim rst As Recordset
Dim dept As Integer

sqltxt = "SELECT DepartmentID from tblDepartment where Department like '*" & Me.txtDepartment.Value & "*'"
Set rst = CurrentDb.OpenRecordset(sqltxt)
dept = rst!DepartmentID
Stop
End Sub

The above script is showing an Error if the input (CEO's) is passed to this query. The error is;
Syntax Error(Missing Operator) in Query Expression ‘Department like ‘*CEO’s*’.

How I can manage the Sing quote in data?
Thanks
Ismail

 

[arnelgp]

sqltxt = "SELECT DepartmentID from tblDepartment where Department like " & Chr(34) & "*" & Me.txtDepartment.Value & "*" & Chr(34)

 

[boerbende]

OR replace ' with ""
sqltxt = "SELECT DepartmentID from tblDepartment where Department like ""*" & Me.txtDepartment.Value & "*"""

 

[CJ_London]

or use the replace function to replace a single quote with two single quotes

sqltxt = "SELECT DepartmentID from tblDepartment where Department like '*" & replace(txtDepartment,"'","''") & "*'"

note you do not need me. or .value as they are the defaults

 

[MarkK]

The most robust solution that lets you use all characters, single AND double quotes, and doesn't require replacements or substitutions, or any messing about with delimiters? QueryDef.

Private Sub Test12930740194381243()
    Const SQL As String = _
        "SELECT DepartmentID " & _
        "FROM tblDepartment " & _
        "WHERE Department LIKE [p0]"
    Dim dept As Integer

    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = "*" & Me.txtDepartment & "*"
        dept = .OpenRecordset.Fields(0)
    End With

    MsgBox "DeptID = " & dept

End Sub

So we create the temp QueryDef in the With block, set the parameter, and run QueryDef.OpenRecordset to get at the data.

One flaw with this approach though is that recordset could easily contain more than one record.

 

[ismailr]

Thanks Boerbende, CJ and Mark.