Select Querry looking for formatts

[s.booth]

How can you set the criteria in a querry to look for all data that starts with two letters then two numbers

 

[AncientOne]

Left([MyField],2) Like "[A-Z]*" And Mid([MyField],3,2) Like "[0-9]*"

 

[s.booth]

Thanks for that, but could I just qualify the info you sent

Left([MyField],2) Like "[A-Z]*" And Mid([MyField],3,2) Like "[0-9]*"

I take it the first part, Left([MyField],2), looks at the first two parts of the field. Could you just explain what, Mid([MyField],3,2), looks at, ie what does the 3,2 mean.


Thanks for you anticipated help.......

 

[AncientOne]

It means that starting at the third character, two characters (3rd and 4th)are inspected for characters between 0 and 9.

 

[s.booth]

Thanks

 

[EMP]

I haven't tested it but I believe

Left([MyField],2) Like "[A-Z]*" And Mid([MyField],3,2) Like "[0-9]*"

can only mean the first character is a letter and the third character is a number.

 

[AncientOne]

Well, I did test it, and guess what?

 

[EMP]

Guess what?    I am right and you are wrong.

The query:-
SELECT TableName.*
FROM TableName
WHERE Left([MyField],2) Like "[A-Z]*" And Mid([MyField],3,2) Like "[0-9]*";

in the attached DB returns these three records:-
RecordID MyField
3           x99yyy
4           ab12xyz
6           a1123

from these six records in the table:-
RecordID MyField
1           aaaaaa
2           111111
3           x99yyy
4           ab12xyz
5           abc1xyz
6           a1123

 

[pcs]

try this:

Val(Mid([YourField],1,2))=0 And Val(Mid([YourField],3,2))<>0)

 

[AncientOne]

... Yes, guess what, I was wrong because I didn't test it properly. But then, I sat down and came up with an answer: maybe not the best or only answer, but it seems to work.

Left([MyField],1) Like "[A-Z]*" And Mid([MyField],2,1) Like "[A-Z]*" And Mid([MyField],3,1) Like "[0-9]*" And Mid([MyField],4,1) Like "[0-9]*"

I think that's the constructive way to tackle an error.

 

[EMP]

The simplest expression for two letters then two numbers is:-

[MyField] Like "[a-z][a-z]##*"