MS Access - Query Help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ms2BSwagg
    New Member
    • Jun 2015
    • 4

    MS Access - Query Help

    Hi,

    I Need to show a product code with a unique entry in a table.

    Eg – Xxxxx Xxxx – Thai Chocolate Tea = XX-TCT

    Regards
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

    #2
    I'm assuming that the Xxxxx Xxxx is one field and the Thei Chocolate Tea is another field? I don't know of a way to do this using only SQL. I think that you will have to build a function in VBA that will return the values you are wanting and then call this function from your query. The VBA function would look something like this:
    Code:
    Public Function GetInitials(FullText as String) As String
    Dim strWords() As String
    Dim strInitials As String
    Dim i As Integer
    
    strWords = Split(FullText, " ")
    
    For i = 0 To UBound(strWords)
        strInitials = strInitals & Left(strWords(i), 1)
    Next
    
    GetInitals = strInitals
    End Function

    Comment

    • jforbes
      Recognized Expert Top Contributor
      • Aug 2014
      • 1107

      #3
      Seth's approach is more straightforward , and I doubt this will be useful, but it might and I find it pleasant and slightly twisted at the same time to use RegEex. So using this function:
      Code:
      Public Function trimWithRegex(ByVal sTemp As String, ByRef sPattern As String) As String
          Dim RegEx As Object
          Set RegEx = CreateObject("VBScript.RegExp")
          RegEx.Global = True
          RegEx.Pattern = sPattern
          trimWithRegex = RegEx.Replace(sTemp, "")
      End Function
      This would be the results from the Immediate Window:
      Code:
      ?trimwithregex("Xxxxx Xxxx – Thai Chocolate Tea", "[^A-Z–]")
      XX–TCT
      This is a pretty good site for developing RegEx Patterns: http://regexr.com/

      Comment

      • zmbd
        Recognized Expert Moderator Expert
        • Mar 2012
        • 5501

        #4
        Keep in mind that RegEx is not a referenced library by default installation in Access. This must either be late bound or the reference added under the VBA-Editor under tools>Reference s>"MS VBScript Regular Expressions"

        Ms2BSwagg: Actually, what you are asking for here is somewhat difficult to follow forcing us to guess as both Seth and Jforbes have done an admirable job.

        If you are using newer versions of Access you might find the following to be helpful.... I use this style of string work quite often in Access to provide custom lot/serial numbers for customers... Sample expressions to extract portion of a text string in Access
        Yes, these are used in the SQL/Query editor :)
        Pay particular attention to the seventh and eighth examples. In the eighth example if there is no middle initial it will return an error.


        Please help us to more fully understand what you are attempting to do here... are these codes fixed, are you updating a table, etc...

        Help us to help you by providing some more context.

        Comment

        • Ms2BSwagg
          New Member
          • Jun 2015
          • 4

          #5
          Hey guys, thanks to you all for the feedback. Will have to go and try out the different suggestions.
          @zmbd i have a work in progress database that i have for a business and need to have it show the name of company (1st letter of the two names = XX and then the product name = TCT) bearing in mind the product name will be various lengths. I would send the file but is large and still incomplete, going thro it as i find errors.
          Thanks again for the help.

          Comment

          Working...