Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37

    Unanswered: Need code adjusted to be able to find Jeff and Jeffrey.

    --------------------------------------------------------------------------------
    This code is a part of flat file database created with MsAccess2000 called “Communications” When a name is entered into a field named “Subject” it will check a table called” Warrant Log” to see if the person being entered is in the warrant log table. If there is no match then there is no action taken, if there is a match then a message box will appear. The problem is the code is very specific, by that I mean it will search for exactly what is entered with no variation other than it is not case sensitive. Example:

    It will locate Jeffrey Smothers or jeffrey smothers it will not Jeffrey B Smothers, Jeffery Smothers, Jeff Smothers or any other variation. I may be asking for something that can’t be.

    Below is the complete code I am working with; it works fine as long as everything is exact (not case sensitive), I have been working for days trying to code it to be a little more forgiving because of variations in name spelling and middle name / initials, issues (sometimes excluded / sometimes not). Any and all help will be greatly appreciated.

    Carbo



    Private Sub subject_LostFocus()

    If IsNull(DLookup("[Name]", "Warrant Log", "[Name]='" & Me![subject] & "'")) Then
    Exit Sub
    End If

    Dim nicholas As String

    nicholas = MsgBox("Possible wanted person entered in the Subject field. Would you like GDB to check?", vbYesNo, "Warrant Check")
    If nicholas = vbYes Then


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Advisory_Warrant"

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

    Exit_Command1778_Click:
    Exit Sub

    Err_Command1778_Click:
    MsgBox Err.Description
    Resume Exit_Command1778_Click
    End If
    Last edited by mshelley1; 05-05-06 at 19:05. Reason: Clarification

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is the scope of this project? You might want a little more functionality then what "LIKE" can offer...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    The purpose of this flat-file, front-end back-end database is provides data management for a law enforcement agency, it has a Communications form used to document general police activity such as traffic stops, field interrogation, security checks etc., a warrant log form used to maintain information on all wanted persons, and a master name file which general information from all types of officer generated paperwork is entered. The particular code that in question serves the Communications form and is activated when the subject field loses focus the subject is where names of persons are entered for various reasons. When a name is entered the code queries the Warrant Log (Table Name) to search for the name that is being entered.

    Any type of code that will perform this function will be welcome.

    Thanks

    Carbo

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How big is the repository of names being searched? How big of a concern is performance vs. accuracy?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2006
    Location
    Alabama
    Posts
    37
    Typically the Communications table will contain between 40-70 thousand records (text) and the Warrant Log Table will contain between 500-2500 records. While accuracy is important it by no means has to be 100% as the operator is alerted to the “Possibility” of a wanted person, it is incumbent on that operator to validate the actual warrant. It’s design mission (search code) should be similar in nature to that of a soundex system. However any variation would be welcomes. In short, we are trying to avoid wanted persons going unnoticed because it is not time effective to have operators run checks on every person they enter into the system thus some people get away.

    Coutinued thanks

    Carbo

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm not a fanatic of soundex... the standard algorithm makes the potentially dangerous assumption that the first character of any entry is correct (e.g. that a spanish "Jose" will never be entered as it is pronounced).

    in one of my apps i use a home-made modif of soundex (illustrated here). it works for me in my situation, but i'm not making any other claims for it.

    there are thousands of articles on the net about alternative "sounds like" searching - google into e.g. "metaphone" (but be warned that a lot of the stuff is either heavy maths theory or some flavour of C code).

    calculating a soundex (or my-fake-soundex) or metaphone will take some time on your 70k rows: this is probably one of those cases where you could break the rule about storing calculated results in your table and store the "sounds like" calculated value. just make sure that any screen capable of editing the name remembers to recalculate the code.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •