Values as "Strings" vs. Properties as "Strings"

[ajetrumpet]

Here is yet another mind-boggling thread by the infamous non-IT professional...

I am trying to understand one idea, and I'm almost there. Here is what I don't understand: When I am printing field names, Case Sensitivity seems to be a factor. Assume my fields are called Field1, Field2, and Field3. This will print the field name Field1...

  For Each fld In rs.Fields
    x = fld.Name
      If x = "Field1" Then
        Debug.Print x
      End If
  Next fld

On the other hand, this code will not print the field name...

  For Each fld In rs.Fields
    x = fld.Name
      If x = "field1" Then
        Debug.Print x
      End If
  Next fld

Does this Case-Sensitive issue have to do with the fact that I am referencing a property instead of an actual value? Thank you for any input on the issue.

 

[pono1]

Probably not. More likely just a rule written and then coded into the guts of Access recordsets. Also, a propellor head looking at the code below might say fld is an item in the fields collection (rather than a property). It's all Geek to me, though...

Regards,
Tim

 

[Banana]

I don't have access to Access right now (sorry for bad pun) so this is off the top of my head.

At top of the module, you have this:

Option Compare Database

Which basically tells VBA to compare data based on the sort order specified in your Option dialog. There are two other options:

Option Compare Text

OR

Option Compare Binary

Comparing on Text makes the comparison case insensitive, whereas Binary would make it case sensitive. Right now, it looks like your database "prefers" Binary comparsion and therefore case sensitivity is important. o

They can be overridden on the fly by calling a function, but I can't look it up right now.

 

[ajetrumpet]

Comparing on Text makes the comparison case insensitive, whereas Binary would make it case sensitive. Right now, it looks like your database "prefers" Binary comparsion and therefore case sensitivity is important.

Good points!!

Where does the issue of property vs. value come into play though? I can be looking at the same "option" statement at the top of a module, and still get one print that returns a case-sensitive string only, and another return that simply ignores it. These returns are of course, a property string and a value string, respectively.

 

[Banana]

Are you doing something differently? I ask because the code you posted are identical with field1/Field1 being the only difference, meaning you are comparing a value in both case.

 

[ajetrumpet]

Banana,

I am just trying to figure out what role a form control has in the following situation...It seems to be the problem, and I don't know why!

The following will not print the name of Field1 (if the value in the control reads "field1")...

Dim rs As Recordset
Dim fld As Field
Dim CtrlValue As String
Dim x As String

Set rs = currentdb.OpenRecordset("MyTable")

CtrlValue = Me.Control

  For Each fld In rs.Fields
    x = fld.Name
      If x = CtrlValue Then
        Debug.Print x
      End If
  Next fld

However, the following Sub will print the name (if the value in Me.Control matches the case-sensitivity of the actual field name in the table - e.g. here, Me.Control's value is "Field1"...

Dim rs As Recordset
Dim fld As Field
Dim CtrlValue As String
Dim x As String

Set rs = db.OpenRecordset("MyTable")

CtrlValue = Me.Control

  For Each fld In rs.Fields
    x = fld.Name
      If x = CtrlValue Then
        Debug.Print x
      End If
  Next fld

What do you make of this??

 

[Banana]

As I said earlier, it's clear that it's comparing binary (e.g. case sensitive), and has nothing to do with whether it's a value or a property.

If you want to find out whether there's a difference between a value and a property, here's what you would do:

Assume the rst's Field name is called Field1, and control's value is "Field1"

x=CtrlValue
y="Field1"

If x=y then
   debug.print "True"
   if x=rst.Field1 then
      debug.print "True, too"
   end if
end if

Now, repeat it with CtrlValue='field1', and y='field1'. You will see that x still will equal y and print out "True" but will fail on "True, too" because field1 != Field1.

It all goes back to the setting you have for the database; it's preferring a binary comparsion and therefore case sensitivity is important. If you need to override it, use a function (the name escapes me for now) to indicate to VBA to ignore the case.

 

[RoyVidar]

As is said above, case sensitivity is determined by the Option Compare statement at the top the module in which the code runs. If omitted, it defaults to Binary (which is case sensitive). Your two subs are proably located in different modules, where one module has Option Compare Database, the other something else (or no Option Compare statement at all). Else, there's something wrong (corruption?), I think.

Beg's the question, doesn't it, whether it would be relevant or interesting to use StrComp in stead of just "String" = "string" in comparisions. Using that, you can specify how to compare - check out the help file, or as some like to do Ucase("String") = Ucase("string"), in case one will tweak the options at a later point of time.