Case sensitive WILDCARD search

[Ann Scharpf via AccessMonster.com]

I have created a database that examines a data table created from a batch
program execution system called Control-M. I have just been asked to add an
error condition that checks a username parameter for uppercase letters.
Uppercase letters cause the program to fail.

I checked the postings here and see that InStr is the way to do a case
sensitive search. My problem is that I don't have any particular WORDS I can
search for, since these are user-defined usernames. Do I have to string 26
InStr's together so I can check for every capital letter? Is there some
easier way to do this that I am missing?

Thanks for your help.

Ann Scharpf

 

[Guest]

You can search on first character by creating a field FLtr: Asc([Control])
and use criteria of Between 65 and 90 for the field.

Some last names use a lower case for first character such as "de La Garza"
that may be entered as "de La GARZA." In these cases you would need a field
like 4thLTR: Asc(Right(Left([Control],4),1)) and same criteria.

Can you do an update to the field to LCase([YourField]) and put all
characters in lowercase?

 

[John Spencer]

You can take a look at the StrComp function or use Instr

strComp("AAA",LCase("AAA"),vbBinaryCompare) will return -1 If it were a
match it would return 0 (strcomp can also return 1 and null)

Instr(1,"AAA",LCase("AAA"),vbBinaryCompare) will return 0 If it were a
match it would return 1 (instr can also return null)

If you just need to force everything to lower case, you could just surround
the prompt with an LCase to force it all to lower case.

 

[Ann Scharpf via AccessMonster.com]

I can't use lcase() to convert the data because this is just a "dump" of
another system. I cannot create a corrections file for them to import back
to control-m. I ended up creating 26 fields like A: InStr(1,[Fld],"A",0).
Checked for each capital letter. Then fed that query to another query where
I selected results based on any of the letter fields' having a positive value.
Kludgy and slow but it is working.

You can search on first character by creating a field FLtr: Asc([Control])
and use criteria of Between 65 and 90 for the field.

Some last names use a lower case for first character such as "de La Garza"
that may be entered as "de La GARZA." In these cases you would need a field
like 4thLTR: Asc(Right(Left([Control],4),1)) and same criteria.

Can you do an update to the field to LCase([YourField]) and put all
characters in lowercase?

I have created a database that examines a data table created from a batch
program execution system called Control-M. I have just been asked to add an

[quoted text clipped - 10 lines]

Ann Scharpf