By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Searching a record with special Characters

repath
P: 14
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
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 5 '10 #1

✓ answered by 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

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
The following approach will work:
Expand|Select|Wrap|Line Numbers
  1. Dim strLastName As String
  2.  
  3. strLastName = "O'Neil"
  4.  
  5. MsgBox DCount("*", "Employees", "[LastName] = """ & strLastName & """")
Jan 5 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. strWhere = Replace("[CustRegion]='%R' AND [CustName]='%N'", _
  4.                    "%R", Me.cmbCustRegion)
  5. strWhere = Replace(strWhere, "%R", _
  6.                    Replace(Me.lstRMName.ItemData(Me.lstRMName.ListIndex), _
  7.                            "'", "''"))
  8. Me.lblOrderCount.Caption = "Total Orders Placed: " & _
  9.                            DCount("CustName", "backUpData", strWhere)
Jan 5 '10 #3

Expert Mod 2.5K+
P: 2,545
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
Jan 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.