REPLACE to remove spaces from postcodes 1

[Cnnx]

I am trying to remove spaces from a postcode field. After searching the various threads I have come across the problem associated with using REPLACE directly in Access so I followed the suggestion of declaring a public function in VB. However there is a compile error (expected identifier) in the code which I do not understand. Please can anyone tell me where my mistake is? - I'm not very confident with VB.

Access 2000
VB v.6

VB code:
Public Function DoReplace(InSightPostcode As String," " As String,"" As String) As String
DoReplace = Replace(InSightPostcode, " ", "")
End Function

Access query:
UPDATE BPCOrInSightTbl SET InSightPostcode = DoReplace(InSightPostcode, " ", "");

 

[M8KWR]

if you use this code in a module

Function RemoveSpaces(ByVal AlphaNum As Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ = " " Then
      
      Else
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveSpaces = Clean$

End Function

this will do the trick for you.

Any other issues let me know

 

[Cnnx]

Thanks M8KWR
Although I can sort of see what the code does I am out of my depth in implementing it. Does AlphaNum stand for the field name? Also how is the table specified in the function?

I saved your code as a Function with the field name in place of AlphaNum and called it using a macro. However I get the error 'Microsoft Access can't find the name 'ByVal' you entered in the expression'.

Sorry if this is really basic stuff but I haven't found the Help files very useful on this.

Function RemoveSpaces(ByVal InSightPostcode As Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(InSightPostcode) Then Exit Function

   For Pos = 1 To Len(InSightPostcode)
      A_Char$ = Mid(InSightPostcode, Pos, 1)
      If A_Char$ = " " Then
      
      Else
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveSpaces = Clean$

End Function

 

[M8KWR]

what to do it change the code back to my original post.

Save this code as a new module, called it Module1, for ease.

Now it depends where you want to alter the postcode field:-

- after the user has put it in, and make sure there is no space's within the field.

- do you want to query a table and remove the space

If you can let me know this thne i gave give you want you need.

Do not worry about not knowing what to do, if you follow what i suggest then it will all work fine. Its a good way to learn, to see how things work, and why they work.

 

[Cnnx]

Thanks M8KWR
I want to run an update query on a table to remove spaces from the postcode field (so that I can match it with some 'unspaced' postcodes from another source)

 

[M8KWR]

your sql code would be.

UPDATE "Table_Name" SET "Table_Name"."Field_Name" = RemoveSpaces([Field_Name]);

all you have to do is change table_name and field_name with the relevant correct names.

If you do not want to alter the sql, then create a new query, change it to a update query. insert your postcode field, and in the "Update to" area, all you would need to enter is: -

RemoveSpaces([field_name])

Again you would need to replace field_name with your postcode field.

Any more issues or problems let me know.

 

[Cnnx]

Wonderful! Exactly what I want. Many thanks for your patience M8KWR. Never realised it would be that simple.

 

[MichaelRed]

Seems a bit much:

? Replace("MyDog Has Fleas", Space(1), "")
MyDogHasFleas

as the basic function works quite reasonably. Of course I'm probably not understanding the greater complexity somewhere.





MichaelRed