Remove Extra Blank Spaces between Postcodes

[DavidAC]

Hi i have a problem with imported data in ACCESS97. The field sometimes contains multiple spaces between the first and last part of the postcode eg DL17 8DD. however in order to run the query correctly there needs to be only one space between the first and last part of the post codes. is there any way this can be done as not all first parts of the post code are 4 characters in length.
:D

 

[Corticus]

Hi DavidAC,

Using:

Killspace: replace([postcode],"space space","space")

in an expression query will get rid of double spaces. This will not get rid of triple spaces, however. I'll try to post back with a more universal method of dealing with multiple space beyond just two.

Use a space in place of my space and double space in place of space space.

HTH,

 

[DavidAC]

Thanks for that however when i try to put it into the query it does not work, do i need to use it in VB code or how do i use it in a expression query?

 

[Corticus]

Sorry,

Create a query and add all the fields you want in it. Use the wizard if necessary. Switch the query to design view (2nd choice in the top left of the main db window). Go to the first blank field, and enter:

killspace: replace([postcode],"space space","space")

Replace [postcode] with the name of the field you need to get the zeros out of.

Replace "space" with a space.

View the query in datasheet view and you should have a new column with data in the form you want. I never did figure out how to get rid of anything but 2 spaces (3 spaces, 4 spaces, etc), but I think that's all you needed. If you need me to e-mail you an example, let me know (send an e-mail or pm) and I'll send you one.

I've got to go home now, but I'll check back before tomorrow.

HTH,

 

[DavidAC]

Thanks for that however when i tried it this morning ACCESS97 informed me that Replace was an undefined function in the expression. When i check the functions available it does not appear in the list. Please Help.

 

[Corticus]

Sorry,

I didn't know you couldn't use replace in Access 97. I don't know what functions you can use in this version so I'll have to do a little research before I can answer you.

-C

 

[Corticus]

Okay,

I think you will need a user defined function. Open your db, and select Tools|Macro|Visual Basic Editor. In the VBA editor, select insert|module. You should see a blank module window thing pop up on the right side of your screen. At the top of this window, it should say 'Option Compare Database'.

Cut and paste the following code into this module, right underneath where it says 'Option Compare Database'.

Public Function stringrep(ByVal Valuein As String, ByVal WhatToReplace As String, ByVal Replacevalue As String) As String

Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)

Do While P > 0
    Temp = Left(Temp, P - 1) & Replacevalue & Mid(Temp, P + Len(WhatToReplace))
    P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop

stringrep = Temp

End Function

Basically, you've created a function, stringrep(), and added it to this db.

Now use this formula in place of replace() in the manner I described in the previous post.

stringrep([yourmodule],"Replace()","stringrep()")

If you need more assistance, here's an example in Access 97:

http://www.theillumni.com/posts/replace97.mdb
(save it to disk before opening)

HTH,