How to Query Lowercase or Uppercase Characters 1

[sawilliams]

I want to query fields where there is a Space followed by a lowercase character. If I use criteria like:

Like * a*

I will get all entries with a space followed by an upper or lowercase letter "a". What can I enter in the criteria field to select ONLY space & lowercase "a"?

Seems like it ought to be easy but I can't find an answer. I have a very messy table of data to tidy. I know how to do a strConv to convert the entire field to Title Case but this database has an audit table that records all changes and who made them. Since my changes are purely cosmetic, I don't want to create tens of thousands of rows in the audit table updating entries that are already Title Case.

Any help would be greatly appreciated.

 

[Remou]

This may suit:

http://support.microsoft.com/kb/209674

http://lessthandot.com

 

[sawilliams]

Thanks Remou.

The "Find Text With an Initial Lowercase Letter Using the Asc() Function" will be most helpful. But the "Find Text With a Lowercase Letter in Any Position Using the StrComp() Function" did not do what I needed or I just don't have a clear understanding of it. I need to find the string [ " " & lowercase a ] for example. I fiddled with both Asc() and StrComp() but could not come up with something that will find that combo of space and a lowercase letter.

 

[shavon]

A regular expression might be useful here?

 

[Remou]

If you are going for a User Defined Funtion, StrComp with vbBinaryCompare is probably easier than a regular expression.

http://lessthandot.com

 

[sawilliams]

Thanks again for the response. If you were going to use a "regular expression" or "StrComp with vbBinaryCompare" could you give me a sample of what I might enter in the criteria field of a query. Just not advanced enough to solve this myself. Many thanks.

 

[sawilliams]

...or maybe what I'm trying to do is not actually doable in the criteria of an Access Query?

 

[Remou]

I thought about this a little, if you put this in the grid:

[tt]InStr(1, [TheField], " c", 0)[/tt]

It will return the position in the string. If the position is greater than zero, the string exists. the final zero is ofr vbBinaryCompare.

http://lessthandot.com

 

[sawilliams]

Remou!

What a great solution! Just excellent. I can't thank you enough. Not only did you give me a great solution, I really learned something as well. You have just made my job much easier.

Many thanks!

 

[Remou]

I am glad you are happy.

http://lessthandot.com