Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Is it possible to modify the FIND command in MS Access

mxracermxracer Member Posts: 2
I use the find command to find users in a table by looking for the userid ex:(yoderr). But if the Userid has a number after it
ex:(yoderr1), the user account that has the 1 after it does not get included in the find. Is it possible to modify this option so that all accounts for a user included userid's with a 1 will be included.

This is the code so far:

Private Sub AllUserInfo_Click()
On Error GoTo Err_AllUserInfo_Click
' THIS IS TO FIND ALL INFORMATION THAT IS SPECIFIED FOR A
' USER BY FOCUSING ON THE USERid

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "User Table Popup"

stLinkCriteria = "[Userid]=" & "'" & Me![Userid] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AllUserInfo_Click:
Exit Sub

Err_AllUserInfo_Click:
MsgBox Err.Description
Resume Exit_AllUserInfo_Click

End Sub

Comments

  • jlegjleg Member Posts: 237
    [b][red]This message was edited by jleg at 2005-2-22 14:38:1[/red][/b][hr]
    [b][red]This message was edited by jleg at 2005-2-22 14:34:20[/red][/b][hr]
    [b][red]This message was edited by jleg at 2005-2-22 14:24:18[/red][/b][hr]
    : I use the find command to find users in a table by looking for the userid ex:(yoderr). But if the Userid has a number after it
    : ex:(yoderr1), the user account that has the 1 after it does not get included in the find. Is it possible to modify this option so that all accounts for a user included userid's with a 1 will be included.
    :
    : This is the code so far:
    :
    : Private Sub AllUserInfo_Click()
    : On Error GoTo Err_AllUserInfo_Click
    : ' THIS IS TO FIND ALL INFORMATION THAT IS SPECIFIED FOR A
    : ' USER BY FOCUSING ON THE USERid
    :
    : Dim stDocName As String
    : Dim stLinkCriteria As String
    :
    : stDocName = "User Table Popup"
    :
    : stLinkCriteria = "[Userid]=" & "'" & Me![Userid] & "'"
    : DoCmd.OpenForm stDocName, , , stLinkCriteria
    :
    : Exit_AllUserInfo_Click:
    : Exit Sub
    :
    : Err_AllUserInfo_Click:
    : MsgBox Err.Description
    : Resume Exit_AllUserInfo_Click
    :
    : End Sub
    :
    I'm not sure if you can perform a complex task with the find command, but you should be able to write an SQL statement or query that will return all users who do not have a 1 at the end of their string.

    SELECT Users.Field1
    FROM Users
    WHERE (((Right([Field1],1))<>"1"));

    This makes the assumption that you are looking for a string, and do not want to include those with "1" at the end. The above may give you a bit of help in developing the correct query.

    Oops Sorry, I should have read your request a bit better. The above will not work for you, but its good practice. You could however use the LIKE statement in your request. Trouble is that you will get exactly that. yoderr, yoderr1, yoderr90, yoderrx, yoderrrrrrrrrrrrrr. But not yoder

    SELECT Users.Field1
    FROM Users
    WHERE (((Users.Field1) Like "yoderr*"));

    or
    Dim strSearch As String
    strSearch = Me![Userid] & "*"
    stLinkCriteria = "[Userid] LIKE " & "'" & strSearch & "'"

    This should work for you.

    The question is how consistent is the data to be searched. Are all the user IDs the same length. Possible 6 characters, corresponding users with numbers 6 characters plus the number.

    I'm pretty sure that you can use a LIKE statement in your FIND. Have a look at using wildcards in your find commands. This should help you tailor a solution

    John ;-)






  • splansingsplansing Member Posts: 140
    You want your search string to be modified just a bit. Try this:

    MyLen = Len(UserID)
    stLinkCriteria = "Left([UserID],MyLen) = """ & Me!UserID & """"

    I'm not sure the where criteria will let you do this, but give it a quick try.


    : : stDocName = "User Table Popup"
    : :
    : : stLinkCriteria = "[Userid]=" & "'" & Me![Userid] & "'"
    : : DoCmd.OpenForm stDocName, , , stLinkCriteria



    : [b][red]This message was edited by jleg at 2005-2-22 14:38:1[/red][/b][hr]
    : [b][red]This message was edited by jleg at 2005-2-22 14:34:20[/red][/b][hr]
    : [b][red]This message was edited by jleg at 2005-2-22 14:24:18[/red][/b][hr]
    : : I use the find command to find users in a table by looking for the userid ex:(yoderr). But if the Userid has a number after it
    : : ex:(yoderr1), the user account that has the 1 after it does not get included in the find. Is it possible to modify this option so that all accounts for a user included userid's with a 1 will be included.
    : :
    : : This is the code so far:
    : :
    : : Private Sub AllUserInfo_Click()
    : : On Error GoTo Err_AllUserInfo_Click
    : : ' THIS IS TO FIND ALL INFORMATION THAT IS SPECIFIED FOR A
    : : ' USER BY FOCUSING ON THE USERid
    : :
    : : Dim stDocName As String
    : : Dim stLinkCriteria As String
    : :
    : : stDocName = "User Table Popup"
    : :
    : : stLinkCriteria = "[Userid]=" & "'" & Me![Userid] & "'"
    : : DoCmd.OpenForm stDocName, , , stLinkCriteria
    : :
    : : Exit_AllUserInfo_Click:
    : : Exit Sub
    : :
    : : Err_AllUserInfo_Click:
    : : MsgBox Err.Description
    : : Resume Exit_AllUserInfo_Click
    : :
    : : End Sub
    : :
    : I'm not sure if you can perform a complex task with the find command, but you should be able to write an SQL statement or query that will return all users who do not have a 1 at the end of their string.
    :
    : SELECT Users.Field1
    : FROM Users
    : WHERE (((Right([Field1],1))<>"1"));
    :
    : This makes the assumption that you are looking for a string, and do not want to include those with "1" at the end. The above may give you a bit of help in developing the correct query.
    :
    : Oops Sorry, I should have read your request a bit better. The above will not work for you, but its good practice. You could however use the LIKE statement in your request. Trouble is that you will get exactly that. yoderr, yoderr1, yoderr90, yoderrx, yoderrrrrrrrrrrrrr. But not yoder
    :
    : SELECT Users.Field1
    : FROM Users
    : WHERE (((Users.Field1) Like "yoderr*"));
    :
    : or
    : Dim strSearch As String
    : strSearch = Me![Userid] & "*"
    : stLinkCriteria = "[Userid] LIKE " & "'" & strSearch & "'"
    :
    : This should work for you.
    :
    : The question is how consistent is the data to be searched. Are all the user IDs the same length. Possible 6 characters, corresponding users with numbers 6 characters plus the number.
    :
    : I'm pretty sure that you can use a LIKE statement in your FIND. Have a look at using wildcards in your find commands. This should help you tailor a solution
    :
    : John ;-)
    :
    :
    :
    :
    :
    :
    :

Sign In or Register to comment.