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

Selecting uppercase part of field

P: 14
Hi,
I have a field with text that is partly uppercase & partly lowercase. I'm trying to select only the uppercase part of the field.

for ex. a field with long description for products. Each description has a part that is not applicable- which is lowercase. I only want the uppercase part of each description.

Any ideas? I am mostly familiar with SQL so solutions in SQL would also be helpful. Thanks.
Jan 29 '10 #1
Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
This code should do the trick. There may be more efficient ways of doing it, but this will work. If your product string has and seperators (Example XLARAR-arar) then you can also do a split which is more efficient.
Expand|Select|Wrap|Line Numbers
  1. getUpper=Split("strInput","-")(0)
Split returns an array, the (0) select first element in that array.

Expand|Select|Wrap|Line Numbers
  1. Public Function getUpper(strInput As String) As String
  2.     Dim intI As Integer
  3.     Dim strUpper As String
  4.     Dim strChar As String
  5.  
  6.     For intI = 1 To Len(strInput)
  7.         strChar = Mid(strInput, intI, 1)
  8.         If StrComp(strChar, UCase(strChar), vbBinaryCompare) = 0 Then
  9.             strUpper = strUpper & strChar
  10.         Else
  11.             Exit For
  12.         End If
  13.     Next
  14.  
  15.     getUpper = strUpper
  16. End Function
Jan 29 '10 #2

NeoPa
Expert Mod 15k+
P: 31,307
From your description I see no way of handling this in SQL. However, your description doesn't explain the situation we need to look at very fully. Perhaps you could provide some example data for us. This is often very revealing as to the actual situation you're dealing with when the ability to explain in detail is limited. You'd be surprised at how often that is.

SQL could be used if there are other distinctions than simply the case of any characters. We'd need those details. Split will (unfortunately) not work directly within the SQL as it involves array processing. Not supported within SQL.
Jan 30 '10 #3

P: 14
Thanks NeoPa, that's my answer for SQL. Yes, the only distinction is the uppercase.
TheSmileyOne- I'm really new to programming & not so familiar with actual programming in Access. How do I implement such code once I save it in a Module?
Jan 31 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Once implemented in a module, you can simply implement it in SQL.

Expand|Select|Wrap|Line Numbers
  1. SELECT getUpper[myField] as ID from myTable;
That will run the function within the the query.

If your using the graphical query editor within Access, it looks a little different.
Example:
Expand|Select|Wrap|Line Numbers
  1. ID: getUpper([myField])
Jan 31 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
The main difference between "native" SQL and SQL that involves VBA functions is that the ladder is somewhat slower. That said, you'd need to be working on 10.000+ records at a time to even notice the difference with such a simple function as the one provided.
Jan 31 '10 #6

NeoPa
Expert Mod 15k+
P: 31,307
@LELE7
I could ask a bunch of precise questions to get the information I'm after, but example data just makes it so much quicker & easier. Never mind. Smiley has you covered anyway.
Feb 1 '10 #7

Post your reply

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