Why is this happening: If Me.Combo6.Value = "CLIENT1" Then wont work

[pedie]

Hi, I know that Combo6 value = CLIENT1 withot any spaces...
however it always returns "MsgBox "No " & Me.Combo6.Value
I also tried If trim(Me.Combo6.Value) = "CLIENT1" Then
Still the same...
Is there some reason this can happen in access form?

Thanks for helping.

[/FONT]
[FONT=Courier New]Option Compare Database[/FONT]
[FONT=Courier New]Private Sub Command8_Click()
 If Me.Combo6.Value = "CLIENT1" Then
 MsgBox "Yes " & Me.Combo6.Value
 Else
 MsgBox "No " & Me.Combo6.Value
 End If
End Sub

 

[boblarson]

Perhaps it's different in Access.

In the Excel VBE Immediate window,

? "Bob" = "bob"

returns False.

Yes, it is different. If you do that in Access it will return TRUE by default.

 

[shg]

Curious.

 

[dk]

Curious.

I think this is because Access will insert an "Option Compare Database" when a new code module is created and that changes the comparison to case insensitive. I believe this behaviour is by default (certainly is my installation). I personally don't like to rely on Option Compare at the module level and prefer to use something like this:

Sub test()

    Dim string1 As String, string2 As String

    string1 = "banana"
    string2 = "BANANA"

    If StrComp(string1, string2, vbTextCompare) = 0 Then
        MsgBox "Same"
    Else
        MsgBox "Different"
    End If


End Sub

That way if the function is copied to another module it will still work as intended regardless of whether the module has the Option Compare or not.

DK

 

[shg]

The question is, what is the result if neither that nor Option Compare Text appears in the module?

 

[xenou]

According to my (2007) help the default is Option Compare Binary:

Option Compare {Binary | Text | Database}

Remarks

If used, the Option Compare statement must appear in a module before any procedures.

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø


Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:

(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)


Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.

This was also a useful short guide to the compare options:
http://support.microsoft.com/kb/98227

And on string comparison in Access:
http://msdn.microsoft.com/en-us/library/aa188509(v=office.10).aspx

 

[xenou]

Pedie,
you may want to be sure the value in the combobox is actually selected and updated as regards the control ...

You may want to actually select the value (don't just use the one that's showing when you open the form), and then try your test with the command button. I think that comboboxes are updated when you make a selection from the combobox list.

 

[shg]

According to my (2007) help the default is Option Compare Binary:

Right. Is the same true in VBA in Access? I would have thought it was a language thing, not an application thing.

Maybe it's just axiomatic (Accessiomatic?) that everyone has Option Compare Database in every module?

 

[xenou]

Every time I've ever created a module in Access Option Compare Database shows up by automatically ... just tried to find an option for this somewhere and I can't (yet). It appears you'd have to go out of your way to change or delete it.

 

[xenou]

This article suggests the same:
http://msdn.microsoft.com/en-us/library/aa164495(v=office.10).aspx

If option compare database is the default for Access Modules it seems the way that it is implemented is in fact by automatically adding this declaration to new modules when they are created ... (!)

 

[xenou]

And one more page that recommends a) generally use Option Compare Database to respect the sort order of the database in Access databases and b) use the StrComp function if you want a robust solution that works the same no matter what Option Compare is used ... (I think this is what dk was saying above).
http://www.fmsinc.com/free/NewTips/VBA/Option/index.html