Convert Excel formula to Access help..?

[towners]

Hi all,

Early days with Access and attempting to transfer some Excel 2010 work over to Access 2010.

I wonder if anyone could help convert a really useful formula I use in Excel that takes a UK Postcode and strips out all but the initial character(s) (I use the result in a lookup to associate the Postcode with a larger geographical area. Postcodes can begin with either 1 or 2 characters i.e. "G1" for Glasgow or "LE1" for Leicester I need the code to consider this.

The code I currently use is:

=IF(ISERROR(VALUE(MID($AK2,2,1))={1,2,3,4,5,6,7,8,9,0}),TRIM(LEFT($AK2,2)),TRIM(LEFT($AK2,1)))

Any help to convert this into a formula I can use in a query would be much appreciated.

Many thanks

Paul

 

[alansidman]

Here is a video on manipulating data in Access within a query.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

 

[Norie]

Try this, replace PostCodeField with the name of the field with the postcode.

Iif(InStr(PostCodeField, " ")=0, PostCodeField, Left(PostCodeField., InStr(PostCodeField, " ")-1))

Note, this will only work if the postcode is the only thing in the field.

 

[towners]

Hi Norrie,

Thank you again for the help.

I've added the formula, the post code is trimmed but I stilll get the first set of numbers.

For example:

"CT18 8HF" is trimmed to "CT18" and I want to only see the "CT"

Similary "B34 7PB" is trimmed to "B34" and I only want to see "B"

Paul

 

[towners]

Thank you Alan, didn't solve my problem (my formula writing limitations...) but very useful nonetheless.

Paul

 

[Norie]

Paul

What are you using that part of the postcode for?

 

[towners]

Hi Norrie,

I want to strip out all but the first charcters as I have a look up table that associates the wider "LE" part of the post code to an area.

I.e.

LE = Area 2
G = Area 1

Rather than have to list all post codes in their entirety.

Regards

Paul

 

[Norie]

If that's what it's for why do you need to strip out this part of the postcode?

How exactly are you using the lookup table?

 

[towners]

Hi Norrie,

It's going to be a simple join to a resource table. My contact has a postcode and I want to put that contact into an area; my resources cover specific regional areas that contain multiple postcode areas (the "LE" bit). So Area 2 would contain post code areas "LE", "CV", "DE" and so on.

So, rather than associate each long postcode (of which there are thousands) with their areas I want to strip out the detailed postcode location information (i.e. the "1 3BZ" from "LE1 3BZ") so I can use the resulting "LE" as the lookup to my resource table.

My Resource table has the column headings:

Post Code Area, Area, Resource

Post code area will be my stripped down postcode i.e. "G" or "LE" and each of these (there are 125) relate to a particular resource area.

Does this help?

regards

Paul

 

[towners]

Got it!

PostCodeArea: IIf(IsNumeric(Mid([InstPostcode],2,1)),Mid([InstPostcode],1,1),Mid([InstPostcode],1,2))

Paul