Only one of two criteria working in query?

[EddyLLC]

Hi All, I'm trying to limit query results from a table to only records within which a specific field contains alpha characters and the field is 4 characters long. Though the field is a text field it may contain "A1" "AA1" "AAA1" and "AAAA". The last "AAAA" is the only record I would want to pull with the query.
I've tried the query below. The alpha criterion works but not the length criterion. It still returns "AA" for example.

SELECT flkpBoat.BoatCode, flkpBoat.BoatName
FROM flkpBoat
WHERE flkpBoat.BoatCode Like "[A-Z]*" AND Len(flkpBoat.BoatCode)=4;

 

[dhookom]

Is flkpBoat an Access table? What do you get with:

SELECT flkpBoat.BoatCode, flkpBoat.BoatName FROM flkpBoat WHERE  flkpBoat.BoatCode  Like "[A-Z]*" AND Len(Trim(flkpBoat.BoatCode))=4;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

The last "AAAA" is the only record I would want to pull
WHERE flkpBoat.BoatCode Like "[A-Z][A-Z][A-Z][A-Z]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EddyLLC]

Thanks for the responses. To answer your question, yes the table flkpBoat is an Access2003 table. It is though a moot point now since your suggested code work great Duane.

Thanks again for the help.

Dave