Len() function for number not returning expected results

[Seth Schrock]

I just used the Len() function on a number and it returned some wacky result. For example, when I did Len(1), it returned 4. If I put quotes around the number, then it returned correctly. Any ideas?

[Luk3r]

The len() function returns one of two values: 1) The length of a string, or 2) the number of bytes necessary to read the variable.

Dim testString As String = "1"
Dim testLen As Integer = Len(testString)

Returns 1

Dim testLen As Integer = Len(1)

Returns 4

[Seth Schrock]

I knew that the LenB() function returned the number of bytes, but I didn't know that the Len() function would do the same on numbers. Based on different searches on Google, it seems like others have been able to use the Len() function in queries to be able to get the number of digits in a number, but maybe that is because it is SQL and not VBA, which is what I'm using.

[Luk3r]

As far as I know in VB/VBA you cannot use the Len() function to get the length of an integer. You would have to convert the integer to a string.

[jforbes]

I think the automatic Type conversion is getting you.

A String is a Reference Type and an Integer is a Value Type. The Len() function takes a String as it's type, so the Int needs to be converted to a String, which means a String Object has to be created. I think the constructor for this includes the sign in the resulting String. So I would expect you to get a String of " 1" for the Integer of 1, and "-1" for -1. I would also expect the result of Len(1) and Len(-1) to be 2, so I'm not sure why you are getting 4. I know that's not an answer, but hopefully it sheds some kind of light. If you want a headache, research boxing and unboxing. =)

You could try:

Len(Trim(intVariable))

Which is pretty much what Luk3r has recommended.

[Seth Schrock]

I had originally done it by converting it to a string, but then I thought I could bypass that step, but evidently not. Oh well. Thanks for your help.

[zmbd]

Seth,
Would you mind posting the code you are using?

Luk3r,
Your code as posted, although valid in VB, is invalid in VBA
Unfortunately, VBA doesn't allow this syntax... would be nice, as there are many times I want to set the default value of the variable and have to do so in two steps.

[Seth Schrock]

I have already erased my code, so I will have to recreate it here with some air code.

Private Function ConvertToDecimal(lngBinary As Long) As Long
'Dim strNumber As String
Dim i As Integer
Dim lngAccumulator As Long
Dim n As Integer
Dim s As String
Dim p As Integer
 
'strNumber = Format(lngBinary, "00000000")
p = 1
For i = Len(lngBinary) To 1 Step -1
    s = Mid(strNumber, p, 1)
    If s = "1" Then
        n = CInt(i) - 1
        lngAccumulator = lngAccumulator + 2 ^ n
    End If
    p = p + 1
Next
 
ConvertToDecimal = lngAccumulator
 
End Function

It would have been something like this. So if I passed the number 1 to the function, the Len(lngBinary) would return 4 and not 1. Passing 5 produced the same result. If I passed 110, it returned 12.

[NeoPa]

This excerpt from the help page for Len() explains it all quite clearly I think :

Len() Help:

Len Function

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len(string | varname)

The Len function syntax has these parts:

  Part    Description 
 string    Any valid string expression.
          If string contains Null, Null is returned. 
 varname   Any valid variable name.
          If varname contains Null, Null is returned.
          If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.

[zmbd]

NeoPa, I do believe that you've hit the nail on the head so to speak :0)


I suspected this was the case; however, I wanted to be sure I hadn't missed anything

[NeoPa]

Thank goodness MS are making such good progress bringing the Help System back to a usable state.

I'm still using 2003 so that's mostly easy for me. I understand many people nowadays tend to avoid it and use alternative ways of finding things out, but that's changing.

The Help System should be your friend, and it can be again in Access.