Null value in field 1

[lmcc007]

I have code to check for duplicate company names and it works. But there are times when I don’t have a company. They do not want me to use the person name as company name and so on. The code recognizes "" (quote quote) as duplicates.

Below is how the CompanyName field is defined in my table:

CompanyName
Data Type = Text
Default Value = “”
Required = Yes
Allow Zero Length = Yes
Indexed = Yes (Duplicates OK)

How do I say if CompanyName is "" or Null, ignore the field?

Below is the code I am using:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("CompanyName", "Company", _
"CompanyName = """ & Me!txtCompanyName & """") > 0 Then
MsgBox "Company name is already used.", , "Company name search."
Cancel = True
End If
End Sub

Any help is appreciated. Thanks!

 

[PHV]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Trim(Me!txtCompanyName & "") <> "" Then
  If DCount("CompanyName", "Company", _
          "CompanyName='" & Me!txtCompanyName & "'") > 0 Then
    MsgBox "Company name is already used.", , "Company name search."
    Cancel = True
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lmcc007]

Great! Just what I needed.

Thank you!