MS Access - Query Help

[Ms2BSwagg]

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]

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:

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

[jforbes]

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:

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:

?trimwithregex("Xxxxx Xxxx – Thai Chocolate Tea", "[^A-Z–]")
XX–TCT

This is a pretty good site for developing RegEx Patterns: http://regexr.com/

[zmbd]

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>References>"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.

[Ms2BSwagg]

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.