Problem with Query

[SteveE]

Wonder if anyone could help me with this one?
I have to extract from a regular text file update the Post Code, the text file comes in the following format
ie: AB101BD199512394208062000QA28011791SN9S00 called [F1]in my table / query. The only data I need is the first 7/8 chr ie AB101BD BUT for this to be a reconigised PostalCode the format should be AB10 1BD I have a query which takes the easy first portion from the example Left 7 and gives me [PostalCode] AB101BD
I need then to convert this to a reconigised [PostCode] so another formula reads this and should produce my answer with the space inserted in the corect place.

PostCode: IIf([PostalCode]=5,Mid([F1],1,2)+" "+Mid([F1],3,3),IIf([PostalCode]=6,Mid([F1],1,3)+" "+Mid([F1],4,3),IIf([PostalCode]=7,Mid([F1],1,4)+" "+Mid([F1],5,3),"NO")))
NB some post codes are 5, 6 ,7 chrs with a space at 3,4 I have attached a small sample to review any help or advise appriciated.

 

[namliam]

What attachement?

It shouldnt be to hard to do in a function... But I dont know how UK zipcodes work...

 

[SteveE]

Ok thanks for the help I have just another problem:
I have now got my Post Code makeup correct but I now need to produce a sector from them. The postcodes can be in the following format:
Post Code The Sector for each of these
A9 9AA ----- A99
A99 9AA ----- A999
A9A 9AA ----- A9A9
AA9 9AA ----- AA99
AA99 9AA ----- AA999
AA9A 9AA ----- AA9A9
The rule is, all the chars from the first section plus the first char after the space.
Any help appreciated.

 

[namliam]

Try this function, really not all that hard

Function UK_Sector(PostalCode As String) As String
    Dim SpacePos As Integer 'position of the space
    SpacePos = InStr(1, PostalCode, " ")
    UK_Sector = Left(PostalCode, SpacePos - 1) & Mid(PostalCode, SpacePos + 1, 1)
End Function

? UK_Sector("A9 9AA")
A99

 

[SteveE]

Thanks for the reply:
When I try to run this function UK_Sector([NewPostalCode]) in my query all I get is "undefined function 'UK_Sector' in expression" where am I going wrong ? my field in the query is : Sector: UK_Sector([NewPostalCode])

 

[namliam]

You did copy paste this into a module? Try adding Public infront of the Function...

If that fails, check the references (if you dont know how to, search the forum)

 

[SteveE]

Function Problems

I did copy the function into a new module and placed first private then public in front but till no joy! Also I had basic functions working in my queries i.e. Left , Len, Repace on my work system, brought my DB home to work on over the weekend (sad!!) but now I,m getting the same problem with the basic functions, I can see no missing references or any other reason why?

 

[namliam]

The 'normal' functions failing must be a missing reference (or a bodged installation)

If you try the UK_Sector thing in debug mode does it work? It may be a data problem?