Check length of string which includes asterisks

[Mr Smin]

Hello,

I want to enforce a minimum string length in a search. The following code gives the messagebox when the text box txtNameSearch is empty, but proceeds to fill the listbox with results if the text box has one or more characters in it.
Is there some special issue with asterisks in this context?

strName = Chr(34) & "*" & txtNameSearch & "*" & Chr(34)
If Len(strName) < 5 Then 
Dim intMsgBoxResult As Integer
intMsgBoxResult = MsgBox("Enter 3 or more letters of the name", vbOKOnly + vbExclamation, "Need More Letters!")
Else
'create a record set and put it in a listbox

 

[vbaInet]

By the way, in your code strName is 4 characters already.

What is the minimum number of characters expected from the user?

Let's see the rest of the code.

 

[Mr Smin]

You've pointed out my schoolboy error! I'd forgotten to count the single quotes. By requiring at least 7 characters I now get the right result.
I'm posting the code anyway, since you asked.
Thanks for the heads-up.

Private Sub btnPanelNameSearch_Click()

Dim RsNames As DAO.Recordset
Dim strName As String

strName = Chr(34) & "*" & txtNameSearch & "*" & Chr(34)

If Len(strName) < 7 Then
Dim intMsgBoxResult As Integer
intMsgBoxResult = MsgBox("Enter 3 or more letters of the name", vbOKOnly + vbExclamation, "Need More Letters!")

Else
Set RsNames = CurrentDb.OpenRecordset("SELECT tblUsers.UserID, tblUsers.firstname, tblUsers.surname, tblUsers.dob FROM tblUsers WHERE (((tblUsers.surname) Like " & strName & "))ORDER BY tblUsers.surname, tblUsers.firstname;")

Set Me.lstUsers.Recordset = RsNames 

End If
End Sub

 

[vbaInet]

Glad to know you figured it out.

Perhaps a slight amendment:

Private Sub btnPanelNameSearch_Click()

    Dim RsNames As DAO.Recordset
    Dim strName As String
    
    If Len([COLOR=Red]Me.txtNameSearch & ""[/COLOR]) < [COLOR=Red][B]3[/B][/COLOR] Then
        Dim intMsgBoxResult As Integer
        intMsgBoxResult = MsgBox("Enter 3 or more letters of the name", vbOKOnly + vbExclamation, "Need More Letters!")
    Else
       [COLOR=Red] strName = Chr(34) & "*" & Me.txtNameSearch & "*" & Chr(34)[/COLOR]
        
        Set RsNames = CurrentDb.OpenRecordset("SELECT tblUsers.UserID, tblUsers.firstname, tblUsers.surname, tblUsers.dob " & _
                                              "FROM tblUsers WHERE (((tblUsers.surname) Like " & strName & "))ORDER BY tblUsers.surname, tblUsers.firstname;")
        
        Set Me.lstUsers.Recordset = RsNames
    
    End If
End Sub

 

[DCrake]

When building recordsets from single tables there is no need to prefix the fields with the table name, therefore

        Set RsNames = CurrentDb.OpenRecordset("SELECT tblUsers.UserID, tblUsers.firstname, tblUsers.surname, tblUsers.dob " & _
                                              "FROM tblUsers WHERE (((tblUsers.surname) Like " & strName & "))ORDER BY tblUsers.surname, tblUsers.firstname;")

Can be reduced to

        Set RsNames = CurrentDb.OpenRecordset("SELECT UserID, firstname, surname, dob " & _
                                              "FROM tblUsers WHERE (((surname) Like " & strName & "))ORDER BY surname, firstname;")

 

[vbaInet]

Good point from David.

Mr Smin may have just copied it straight from his query because this is the default way an sql statement is built when you use the Query Wizard.

 

[Mr Smin]

I did just that - does it make a difference to Access or is the shorter form just easier to read?

 

[DCrake]

Not only is it easier to read it is easier to spot mistakes.