Searching a record with special Characters

[repath]

Hi,

I use DCount function to count the number of entries for the existing customer by name of the customer in the database table.

Table Structure like: CustRegion, CustName, OrdersPlaced.

One of the customers has a name like O'Neil

Me.lblOrderCount.Caption = "Total Orders Placed: " & DCount("CustName", "backUpData", "[CustRegion] = '" & Me.cmbCustRegion.Value  & "' and [CustName] = '" & Me.lstRMName.ItemData(Me.lstRMName.ListIndex) & "'")

but when searching it reads like

CustName = 'O'Neil" (Neil is being ignored and is being displayed as syntax
error.

Can you please help me in searching these names.

I appreciate any help

[ADezii]

The following approach will work:

Dim strLastName As String
 
strLastName = "O'Neil"
 
MsgBox DCount("*", "Employees", "[LastName] = """ & strLastName & """")

[NeoPa]

Firstly, I suggest you check out Quotes (') and Double-Quotes (") - Where and When to use them. They are often used incorrectly in Access, for reasons explained therein.

I would recommend a solution similar to :

Dim strWhere As String
 
strWhere = Replace("[CustRegion]='%R' AND [CustName]='%N'", _
                   "%R", Me.cmbCustRegion)
strWhere = Replace(strWhere, "%R", _
                   Replace(Me.lstRMName.ItemData(Me.lstRMName.ListIndex), _
                           "'", "''"))
Me.lblOrderCount.Caption = "Total Orders Placed: " & _
                           DCount("CustName", "backUpData", strWhere)

[Stewart Ross]

Hi. As an aside to the excellent suggestions offered by my colleagues, could I also add that using the customer's name in the way you are doing is likely to lead to erroneous counts being returned. Customer names are not in any way unique - you can and will have more than one customer in a specified region with the same surname. Also, if you are searching on a surname entered as free text (that is, not retrieved from a master customer record) you are bound to have spelling issues (e.g. variants such as O'Neil, ONeil, O Neil, O'Niel, and so on).

I would recommend that you consider what makes your customer records unique and that you count on the unique key concerned - a customer reference number or the like - and not on the non-unique surname.

If you do not have a unique key defined for your customers you may find it beneficial to review your database design. In this case you may find our insights article on database normalisation and table structures useful.

-Stewart