By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,517 Members | 1,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,517 IT Pros & Developers. It's quick & easy.

MS Access - Query Help

P: 4

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

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

Jun 19 '15 #1
Share this Question
Share on Google+
4 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetInitials(FullText as String) As String
  2. Dim strWords() As String
  3. Dim strInitials As String
  4. Dim i As Integer
  6. strWords = Split(FullText, " ")
  8. For i = 0 To UBound(strWords)
  9.     strInitials = strInitals & Left(strWords(i), 1)
  10. Next
  12. GetInitals = strInitals
  13. End Function
Jun 19 '15 #2

Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function trimWithRegex(ByVal sTemp As String, ByRef sPattern As String) As String
  2.     Dim RegEx As Object
  3.     Set RegEx = CreateObject("VBScript.RegExp")
  4.     RegEx.Global = True
  5.     RegEx.Pattern = sPattern
  6.     trimWithRegex = RegEx.Replace(sTemp, "")
  7. End Function
This would be the results from the Immediate Window:
Expand|Select|Wrap|Line Numbers
  1. ?trimwithregex("Xxxxx Xxxx – Thai Chocolate Tea", "[^A-Z–]")
  2. XX–TCT
This is a pretty good site for developing RegEx Patterns:
Jun 19 '15 #3

Expert Mod 5K+
P: 5,397
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.
Jun 19 '15 #4

P: 4
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.
Jun 24 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.