Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Kansas City, MO
    Posts
    2

    Unanswered: auto-generate unique key from existing fields, like substring_index() in MySQL

    I have been successful auto-generating a "winecode" from different fields in a table, using an update query. Problem is, the winecode is not unique, because I am only able to locate by "left" and "right". What I need is the ability to locate from the first space within a field. Specifically: there are numerous wineries that start with the same word, (like chateau something-or-other) so I need to use the first few characters of the first word, and also the first few characters of the second word, and then I can combine with the rest of my partial fields (where the left and right locator functions are fine). "Mid" doesn't work for this, as far as I can tell, because the words are all of differing lengths. The substring_index() function in MySQL works perfectly for this, but I can't get anything to work in Microsoft Access. Can anyone help me with this? I have looked high and low for the solution....
    Thank-you for your consideration

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: auto-generate unique key from existing fields, like substring_index() in MySQL

    This function will do the trick:


    Public Function WineCode(s As String) As String
    'Returns a string with at most intMaxChar characters after each space of s
    Dim n As Integer, m As Integer
    Const intMaxChar As Integer = 5

    'n is position of first space
    n = InStr(1, s, " ")
    Do Until n = 0
    'Determine position of next space
    m = InStr(n + 1, s, " ")
    If m = 0 _
    Then 'No further spaces after the current one
    WineCode = WineCode & Mid(s, n + 1, IIf(Len(s) - n < intMaxChar, Len(s) - n, intMaxChar))
    Else
    WineCode = WineCode & Mid(s, n + 1, IIf(m - n - 1 < intMaxChar, m - n - 1, intMaxChar))
    End If

    'Set n at position for next space
    n = m
    Loop

    End Function


    Ad Dieleman

  3. #3
    Join Date
    Jan 2003
    Location
    Kansas City, MO
    Posts
    2
    I hate to admit defeat, but I was unable to get this to work in Microsoft Access (which doesn't mean that it isn't correct) and switched my project over to FileMakerPro 5.5, where I found a function called "MiddleWords" which worked nicely. Here is how I used it, if it will help anyone else:

    Left(Winery,4) & Left(MiddleWords(Winery,2,1),4) & Left(Varietal,3) & Left(Appellation,4) & Left(Vineyard,3) & Left(Designation,4) & Right(Vintage,2)

    The combination of the"Left" function with the "MiddleWords" function allowed me to pull "WhisPeak" from the multi-word entry of "Whispering Peaks Vineyards and Winery" from the field of "Winery", and then I could combine it with parts of the other fields to make up a text code unique to each product.

    thank you,
    winedata

Posting Permissions

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