Retreiving postcode

[Paul Clark]

I have a table MembershipList, containg the field PostCode and a form with field Code (contain AA or A).

This code works:
WHERE (((MembershipList.PostCode) Like ([Forms]![frm_membcode]!

 & "*"))
but returns all possible A# and AA codes.

I have found suggested code using: LEFT(my_field, 2) FROM tbl WHERE my_field LIKE '[a-Z][a-Z]%'

Entered in the query SQL translates to this:
SELECT Left([PostCode],2) AS Expr1 FROM MembershipList WHERE ((([Code]) Like '[a-Z][a-Z]%'));

but returns blank!

 

[sneuberg]

The expression like [a-Z][a-Z]% appear to be a regular expression. I don't believe the Access Like Condition supports that. But I don't understand what you are trying to do. Could you provide some sample data showing cases that should be selected and cases that shouldn't.

 

[Paul Clark]

Choosing Area, through a form field, returns the pick Code:

Code Area
AB Aberdeen
AL St. Albans
B Birmingham
BA Bath
BB Blackburn
BD Bradford
The Code is then matched to the first alpha charaters in the PostCode field on the Membership tbl.

 

[arnelgp]

you can use Between:

Where Left(

, 1) Between "A" and "Az"

or

Where [Code] [COLOR=Blue]Alike[/COLOR] "A%"

 

[Paul Clark]

Finally
fixed this.
Original post:
"I have a table MembershipList, containg the field PostCode and a form with field Code (contain AA or A).

This code works:
WHERE (((MembershipList.PostCode) Like ([Forms]![frm_membcode]!

 & "*"))
but returns all possible A# and AA codes."


Created a pre query with fields:
[tbl_postcode]![Code]with criteria ([Forms]![frm_membcode]![Code]) and CLen: Len([Code])
i.e. results BB and 2 or B and 1

Then added two extra hidden field to the main query:
[tbl_postcode]![Code] with criteria ([Forms]![frm_membcode]![Code]) and IsNumeric(Mid([PostCode],2,1)) with criteria IIf([CLen]=1,-1,0)

First fields is always null but it doesn't work without it.