VB Forum / General / March 2005

Man... it's been a while since I've seen "bang" characters <g>... anyway,
have you tried converting everything to lower case before comparing?

'==========
  sFirstName = LCase$(txtFName.Text)
  sLastName = LCase$(txtLName.Text)

  If (LCase$(rsPatient.Fields("FirstName").Value) = sFirstName) _
     And (LCase$(rsPatient.Fields("lastname").Value) = sLastName) Then

     MsgBox "Match"
  End If
'==========

Actually, I'd rather break it up into two separate tests, at least until
debugged.
'==========
  sFirstName = LCase$(txtFName.Text)
  sLastName = LCase$(txtLName.Text)

  If (LCase$(rsPatient.Fields("FirstName").Value) = sFirstName) Then
     'First names match... now compare last names
     If (LCase$(rsPatient.Fields("lastname").Value) = sLastName) Then
        'Ok... both match.. take action
        MsgBox "Match"
     End If
  End If
'==========

You can use LCase$ or UCase$ as Ken suggests or you can use StrComp

If
StrComp(rsPatient.Fields("FirstName").Value,txtFName.Text,vbTextCompare)=0 _
And
StrComp(rsPatient.Fields("LastName").value,txtLName.Text,vbTextCompare)=0
Then

BTW, you can use the ! syntax and assume the default .Text property on the
textbnox if you prefer;  I find it makes maintenance of the code harder and
prefer to spell out the defaults.

You could use the Instr function:

   If InStr(1, "thisHOUSE", "house", vbTextCompare) > 0 Then
       MsgBox "found"
   End If

or you could do it like this, which I believe is faster and that could be
important if
this comparisons happens in a loop:

   If InStr(1, UCase("thisHOUSE"), UCase("house"), vbBinaryCompare) > 0
Then
       MsgBox "found"
   End If

RBS

Convert everything to the same case (be it upper or lower).  For example:

  If LCase$(rsPatient!FirstName) = LCase$(Me.txtFName) And
LCase$(rsPatient!lastname) = LCase$(Me.txtLName) Then

Alternatively, you COULD use Option Compare Text in the modules's
Declaration section. This causes string comparisons to be non-case
sensitive.  Personally, I don't recommend this. But, it is an option so I
wanted to point it out.

Now if I may critique just a bit.....

Avoid using the bang operator (the exclamation point) for field values.
Write out the properties as appropriate. For example, with ADO (not sure if
that's what you're using, but this is just an example):

Instead of:

   rsPatient!FirstName

write:

   rsPatient.Fields("FirstName").Value

It's really much better, and even more efficient. The bang operator does a "
collection lookup", which takes longer than just referencing the properties.
Besides that, code compatibility is more ensured.

Second, NEVER rely on controls' default properties as you're doing with the
textbox (by excluding the .Text property in your code). In VB3 and earlier,
using the default property (or the control's "value") was more efficient,
but this is not so in VB5/6. It also makes your code harder to read. If you
don't know that Text is the default property for a TextBox, the code is
"unclear" to the reader (and if you ever try to "upgrade" the code to
VB.NET, you're looking at many possible problems).  As a rule, don't rely on
defaults at all. One possible exception is the Item property (or method, as
it can be written either way) of a collection. You *should* be able to
always rely on Item being the default of a collection. But, just in case,
you might write code like this:

   rsPatient.Fields.Item("FirstName").Value

You might find DLLs or OCXs having collections that don't make Item the
default. This is rare, but it does happen. IMO, this is the fault of the
programmer who wrote the collection class, but as the user of the class, you
need to be aware of the possibility the author didn't make Item the default.

You can add this line on top of your vb code form.

Option Compare Text

This line won't distinguish between lower case text and upper case text and
the line is for the whole module.

If you need to distinguish between lower and uppercase, then from sub to
sub, you should use Ucase$ or LCase$ and not the Option Compare statement.

Patrick Garceau

In addition to the relies you've already received you may want to Trim() the
value in the textbox before doing the compare.  Since the textbox is a data
entry control someone may enter an extra space or two before or after the
text.

(using Ken's example)
If LCase$(rsPatient.Fields("FirstName").Value) =
Trim$(LCase$(txtFName.Text)) Then

Also, some patient data may be Null so you'll need to test for that
condition before doing the comparison.

HTH,

Jim Edgar

I just have to mention: your question has NOTHING to do with an SQL
statement as your subject line suggests.