Postcode stripping

[ddiver]

I am trying to isolate the identifying area codes from postcodes and want to extract the first one or two letters from each postcode. I have created a query that uses:

SmallPostCode: IIf(Len([PostalCode])>=2,Left$([PostalCode],2),"")

to extract the first two characters of the postcode. But how do I cope with postcodes that have only one letter before numbers start? (eg G66 3DP as opposed to GU66 3DP). Any help would be most appreciated!

D Diver

 

[sportsguy]

Custom function

Copy this function into a module,
then put the function in your query.

sportsguy

Function StripZip(anyZip as String) as String

If len(anyZip) >=2 And Mid(anyZip, 2, 1) < "a" Then
   StripZip = left(anyZip,1)
Else
   StripZip = left(anyZip,2)
End If

 

[ddiver]

Custom Function

OK this looks great and just the job but I just cant get it to work. I havw created a new module and replaced some of the variables to suit my purposes. My code looks like this:

Public Function StripPC(PostalCode As String) As String

If Len(PostalCode) >= 2 And Mid(PostalCode, 2, 1) < "a" Then
StripPC = Left(PostalCode, 1)
Else
StripPC = Left(PostalCode, 2)
End If

End Function

I have saved it and called it StripPC. When I return to the module window it doesnt give me the option to run it. And when I try to refer to it in the query (by just typing in StripPC in the field box?) and I run the query it throws up a parameter box asking me to enter a value for StripPC. Please help! I know I have probably missed something really silly and simple but I feel I am so close and yet so far!

Many thanks
DD

Copy this function into a module,
then put the function in your query.

sportsguy

Function StripZip(anyZip as String) as String

If len(anyZip) >=2 And Mid(anyZip, 2, 1) < "a" Then
   StripZip = left(anyZip,1)
Else
   StripZip = left(anyZip,2)
End If

 

[ColinEssex]

Don't forget to put the postcode field in the function brackets in the query.

StripPC([PostcodeField])

Col

 

[ddiver]

Thanks for your help Col! I have now put Expr1: StripPC([PostalCode]) in the query and get an error message : Undefined function 'StripPC' in expression. What have I done wrong??

DD

Don't forget to put the postcode field in the function brackets in the query.

StripPC([PostcodeField])

Col

 

[ColinEssex]

Just make it a normal Function not a Public function.

I tried it and it does work.

Col

 

[ddiver]

HI again

Sorry to still not be getting it....but I am still getting the above error (Undefined function 'StripPC' in expression). Is there something I need to do to define it? I have created a module by going into the module window in the databse and clicking new. Then I was reading about class modules and so created a class module from the 'insert' menu but both give me the same error.??? So close and yet so far!
DD

Just make it a normal Function not a Public function.

I tried it and it does work.

Col

 

[ddiver]

Whoops sorry I have got it now! I am an idiot. Many thanks for your help and also to sportsguy for the original code!
DD

Just make it a normal Function not a Public function.

I tried it and it does work.

Col

 

[ColinEssex]

Make sure you haven't named the module the same as the function. Also (I don't think this is it though) rename your "PostalCode" in the function to "Pcode" so that its different from the field name

Last thought, check the references and see if any are missing

Col

 

[Uncle Gizmo]

This was covered in a recent thread! (so much for searching!)

 

[ddiver]

Uncle Gizmo I tried and couldnt open your Pcodeanswer1.zip file. It said the databse was unrecognised? I am running access 97 so that maybe be the problem.

This was covered in a recent thread! (so much for searching!)

 

[ddiver]

As an update to my own problem I have got the original query to work. Now I have created a query with my original query (that strips a full UK postcode down to its first one or two letters) and my table of possible postcode starts. I have asked the query where the abbreviated postcode = a postcode in the table to display the postcode area identity number:
PC3: IIf([PC2]=[Postcode],[Postcode ID],0)

this seems to work until I get a 'data type mismatch in criteria expression' After scratching my head about this for a while (they are both text) I have come to the conclusion it is because there are blank fields in the full postcode field of the database. How would I put an 'catch this out' expression in my original code? As a reminder this was:

Function StripPC(PostalCode As String) As String

If Len(PostalCode) >= 2 And Mid(PostalCode, 2, 1) < "a" Then
StripPC = Left(PostalCode, 1)

Else

StripPC = Left(PostalCode, 2)

End If

End Function

Many thanks for all your help
DDiver

 

[Uncle Gizmo]

This is the code from the module: >>>>

Option Compare Database
Option Explicit

Public Function GetPcodePart(strPcode As String)
'>>>>>>>>>>>>>>>>> Str, Start, Length
Dim str1 As String
Dim str2 As String

Dim x As Integer

For x = 1 To 5
str1 = Mid(strPcode, x, 1)

If IsNumeric(str1) Then Exit For
If str1 = " " Then Exit For

str2 = str2 & str1

Next x

GetPcodePart = str2

End Function

 

[ddiver]

Thats great Uncle Gizmo thanks for that. I have tried that and it works brill but it still results in the same problem. I get errors where there is no postcode present in the database. Is there a section of code I can add to that that deals with 'missing' (or null) postcodes in the table? Or would it be easier to get the postcode filled in (I have about 15 missing from 10000 records)

Thanks again
DDiver.

This is the code from the module: >>>>

 

[Uncle Gizmo]

Well, the easiest way would be, (if you can gain access to the fifteen offending records easily) to enter a dummy postcode into these fields something like XX34 XXX would do very nicely. This would return XX for the results which didn't have postcodes , and you may find this handy any way to identify the records without postcodes!

However if you really need the function modified please post back.