Query for Partial Uppercase Text in Field

[pjordan@drcog.org]

Ok I know there are several ways to just convert from lower case to uppercase and I know there are things like Expr1: Asc([CountLocation]) you can use with 80 to find all uppercase records in a query, but mine is a bit different and I'm wondering if it's even possible.

I have a text field called CountLocation. The format for this should be as follows:

MAIN STREET NAME: Cross Street Name

So what I want to do is search all the records that do not have all caps before the :

Then I want to search for all the records that only have all caps after the :

The only way I could think of doing it is, text delimiting the field in say Excel by the : and then putting that as a new table in access and searching that way. Too much work I think and not very efficient. So anyone have a clue how to do a partial search for uppercase and lower case as in my example above?



Thanks

[Robeen]

You can do this in Access - using a combination of the Instr function, the Left function, the Right function and the UCase & StrConv functions - depending on what you want to achieve.

Do you want to just FIND the records?
Or do you want to CHANGE them to the correct format?

[pjordan@drcog.org]

No I just want to find the records since the 2nd part after the : will contain both upper and lower case for street names. So how would I go about using the nstr function and UCase & StrConv (though I did read up on using the Ucase and StrConv to change from one case to another)...

[Robeen]

This is a little on the tricky side [but I'm glad I got a chance to work on it and figure it out]!

What we're going to do is create a new field in your query that will:
1. Use the Instr() function to determine the position of the ":" in the string.

2. Use the Left() function + the Instr() function to get all characters to the left of the ":".

3. Use the UCase() function + Left() function + the Instr() function to convert the characters to the left of the ":" to upper case.

4. Use the StrComp() function to compare:
the result of step 3 [which converts everything to the left of the ":" to upper case] to
the result of step 2 [which does NOT convert the characters to the left of the ":" to upper case].

The StrComp() function [using the 0 to do a binary comparison] returns a 0 if both strings are equal and a -1 if they are not.

We are comparing a string that we have forced to upper case with the same string that has not been forced to upper case.

This is what the new field looks like in my test query [Design Mode]:

 
Is_It_Upper: StrComp((UCase(Left([CountLocation],InStr([CountLocation],":")-1))),(Left([CountLocation],InStr([CountLocation],":")-1)),0)

Put -1 in the Criteria for this new field [to return rows where the two strings do not match].

What this will give you is all rows in which the characters to the left of the ":" are NOT ALL upper case.

Let me know if this works for you.

[Robeen]

If you need to find rows where the text to the right of the ":" is all upper case:

Create a new query and put all your fields in and then add a field with this:

 
Is_It_Upper_Right_Of_Colon: StrComp((UCase(Right([CountLocation],Len([CountLocation])-InStr([CountLocation],":")))),(Right([CountLocation],Len([CountLocation])-InStr([CountLocation],":"))),0)

Put a 0 in the Criteria row for this field.

[pjordan@drcog.org]

Great work Robeen, I will try this out. I figured someone might get a kick of attempting to figure this out. Seems most people just look for field where all the text is all uppercase or all lower case and want it switched to the other. So it was hard for me to find a decent starting place, this is cool thanks...

[Robeen]

No problem.
Mark it solved if everything works as you need.

[pjordan@drcog.org]

The second one works fine, however, I'm getting an "Invalid procedure call" for the first one (all uppercase before the

[Robeen]

They are both working in my test database. I just re-ran the queries.
I even copyied the field from my post back into my query and it works.

Because of the involved syntax, you will have to be certain that you've got everything exact.

[Robeen]

Post what you have in your query here & I'll see if I can spot a problem.

[pjordan@drcog.org]

Ok just took out the -1 criteria and it seems to work (of course giving me all the 0 and -1). When I try and sort the column Is_It_Upper, it gives the "Invalid Procedure Call" again. I have 3976 records if that makes a difference. I have noticed several #Error ones that are missing the ":" so maybe that's the issue and if I fix those it should work...

[Robeen]

The number of records shouldn't matter.
Can you post exactly what you have in that field here?

[pjordan@drcog.org]

I solved the problem. 2 records didn't have the ":" so it was returning #Error for the Is_It_Upper field. When I added the ":" in both those fields, I went back to the query and entered the -1 into the criteria and it worked fine. Thanks for your help, got the queries all working now...

[Robeen]

Hmm . . . will the missing ":" be an ongoing issue?
If so, it might get a little more complicated to make sure that you don't get that error every time you have a row in which that field doesn't have a ":".

All the best!!
Mark as solved if your issue is solved!

Let me know if you have any more questions!

[pjordan@drcog.org]

No not an issue. I had thought I had queried any records missing the ":", but 2 didn't have them still. I could easily page through the records to find the #ERROR so it wasn't much of an issue...