Need "Like" Alpha Alpha Digit

[Bonnie A]

Hi everyone! Using A02 on XP.

I need to pull records based upon the originator which is either 3 initials
(ABC) or a contract number (GP1234). I could say like "GP*" but what if I
have a person with GPS for initials? So, I really want to have a 'like alpha
alpha digit' and that would always identify them. (I don't want to use IIF
LEN because the system is making the field 8 characters.)

I know this is a super simple one but Help is only showing me how to use it
one deep and I need to go three deep. I've searched help all morning but
can't figure it out.

Thank you in advance for your time and assistance!

 

[Banana]

Jet does have some support for regular expression, so you could use
that. If you search in VBA Editor's help for "Wildcard Characters Used
in String Comparisons", you should get a list of metacharacters you can
use to control how the comparsion should be done.

In your case, it would probably be something like this:

LIKE "[a-zA-Z][a-zA-Z]#*"


Alternatively, if it's always 3rd character that will be numeric, it may
be sufficient to do this:

IsNumeric(Mid([MyString],3,1))

Finally, I don't know about how the table is actually structured, but
I'm just slightly concerned it may not be optimally designed as we
shouldn't really be mixing different entities in a single table.

 

[Bonnie A]

Thanks Banana for the info.

I used this one: LIKE "[a-zA-Z][a-zA-Z]#*" because the third character is
a number (the field is a text field).

Exactly what I needed.

And yes, the table is not pro level. I'm allowed to use Access for managing
my work as long as I do it myself. I figure I'm about in Eighth Grade, maybe
a Freshman in my home schooled career. You guys on-line keep me functioning
at a much higher level than I could attain on my own.

With sincere gratitude and appreciation,
--
Bonnie W. Anderson
Cincinnati, OH

Jet does have some support for regular expression, so you could use
that. If you search in VBA Editor's help for "Wildcard Characters Used
in String Comparisons", you should get a list of metacharacters you can
use to control how the comparsion should be done.

In your case, it would probably be something like this:

LIKE "[a-zA-Z][a-zA-Z]#*"


Alternatively, if it's always 3rd character that will be numeric, it may
be sufficient to do this:

IsNumeric(Mid([MyString],3,1))

Finally, I don't know about how the table is actually structured, but
I'm just slightly concerned it may not be optimally designed as we
shouldn't really be mixing different entities in a single table.

Hi everyone! Using A02 on XP.

I need to pull records based upon the originator which is either 3 initials
(ABC) or a contract number (GP1234). I could say like "GP*" but what if I
have a person with GPS for initials? So, I really want to have a 'like alpha
alpha digit' and that would always identify them. (I don't want to use IIF
LEN because the system is making the field 8 characters.)

I know this is a super simple one but Help is only showing me how to use it
one deep and I need to go three deep. I've searched help all morning but
can't figure it out.

Thank you in advance for your time and assistance!

 

[John Spencer]

Like "GP#*"

Should return all records that start with GP and a number



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County