473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selecting uppercase part of field

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
6 2757
TheSmileyCoder
2,322 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
LELE7
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
2,322 Expert Mod 2GB
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
2,322 Expert Mod 2GB
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
32,556 Expert Mod 16PB
@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

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

Similar topics

3
by: Al Findlay | last post by:
Hello! What I need to do is this: "On the fly".... 1. Select part of an image provided by the user (i.e. define a thumbnail) 2. Store this 'thumbnail' in an OLE field in an ODBC database...
6
by: Matik | last post by:
Hello all, I've following problem. Please forgive me not posting script, but I think it won't help anyway. I've a table, which is quite big (over 5 milions records). Now, this table contains...
8
by: Henrik Larsson | last post by:
Hi, I need help with selecting the following rows from a table looking like this: ID IP Query 1 -> 1 2.2.2.2 (ie first IP 1 1.1.1.1 <- Query 2 for each...
5
by: Todd Snyder | last post by:
I need to make a field put uppercase letters in it when a lowercase letter is entered into that field. Is there an expression to use or does it have to be written in code? *** Sent via...
6
by: feeman | last post by:
I can change a field to upper case by using the after event function and the following code Me. = UCase(Me.) But how can you do it so that the whole form will change to Uppercase, there are...
9
by: rickou812 | last post by:
What I am attempting to do is create a form field in which a company name can be selecting from a drop down box. When selecting I want to display the information from my database about the selected...
2
by: t8ntboy | last post by:
I have a table the contains field called PersonID. Each record in the personID field begins with the letter "p" in uppercase or lowercase. I want to run a query that finds all of the lowercase...
7
by: tom harrison | last post by:
i'm so glad i found this forum! i have to have some coursework done by the end of the week and i really need it explaining to me. i'm not really that big on stuff like this but it's a part of my web...
6
by: 182719 | last post by:
<?php $testcase = 'AKLWC139'; if (ctype_upper($testcase)) { echo "The string $testcase consists of all uppercase letters. \n"; } else { echo "The string $testcase does not consist of all...
3
by: =?Utf-8?B?Sm9zZXBo?= | last post by:
Hi all, I'd like to know how to force uppercase characters in an ASP.Net web form text field. Thanks
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.