Len() Function not Including Spaces

[tylerg11]

I am trying to use the Len() function to return the number of characters for a string. However, the function does not return any trailing spaces. Is there a workaround for this? Any other function I could use?

[John_G]

What is the context in which you are using Len()? A quick test showed that it will count the trailing spaces if they are there. For example, len("A ") (3 spaces following 'A') will yield 4, as expected.

However, if you type 'A' followed by 3 spaces into a form field, and then test the length in the After Update, it yields only 1 - trailing spaces are removed.

HTH

John

[tylerg11]

I am using it on a form field, so I am running into that second scenario.

[Missinglinq]

I am using it on a form field, so I am running into that second scenario.

That being the case, as John said, Access automatically removes trailing spaces!

Perhaps if you told us why you're trying to do this, we could suggest a workaround.

Linq ;0)>

[tylerg11]

I have a textbox that is used as a filter box. After the textbox text is changed (i.e. a new character is added) then it filters the subform below. After it filters the subform, it returns to the filter box placing the cursor after the string. However, if a criteria item is typed like "se ", the cursor will go right after the "e". Basically, a space can't be inserted as filter criteria in the text box because the Len() that is returned eliminates the space.

[tylerg11]

Here is the code of what I am trying to do..

Private Sub txtFilter_Change()
 
    If Nz(Me.txtFilter.Text, "") = "" Then
        Me.FilterOn = False
        Me.txtFilter.SetFocus
        Exit Sub
    End If
 
    Me.Filter = "lastName like '" + Me.txtFilter.Text + "%' or userName like '" & _
                Me.txtFilter.Text + "%'"
 
    Me.FilterOn = True
    Me.txtFilter.SetFocus
 
    Me.txtFilter.SelStart = Len(Nz(Me.txtFilter.Text, "")) + 1
 
End Sub

This last line is where I am having issues. If the last character of txtFilter is a " ", then Access trims it out, and so I can never account for a space when setting the cursor location.

[Missinglinq]

You might take a look at Allen Browne's article and hack called "Find as you type" that does what you're attempting, moving to a Record as you enter text:

http://allenbrowne.com/AppFindAsUType.html

Also note that in Access VBA, in your code

Me.Filter = "lastName like '" + Me.txtFilter.Text + "%' or userName like '" & _
                Me.txtFilter.Text + "%'"

the Plus Signs (+) should probably be replaced with Ampersands (&), and the correct Wildcard Character is an Asterisk (*) rather than the Percent Sign (%).

Using the Plus Sign to Concatenate Strings has been retained, for backward compatibility, but it can lead to unexpected results at times.

The Percent Sign is valid for Wildcard characters in Access SQL, I believe, but not in Access VBA code.

Linq ;0)>