Removing spaces from a cell

[Deano1976]

MS Access 97

I thought I could do this easily, but apparently not.

I want to be able to remove blanks from fields.

for example if I have data as follows:

xxx xx
yyyyy
zzz zz

I want to end up with
xxxxx
yyyyy
zzzzz

 

[straybullet]

To get rid of every space permanently, bring up Find/Replace and actually enter a space into the Find: text box. Leave the Replace: empty and be sure to choose "Any Part of Field" in the Match: dropdown.

Let them hate - so long as they fear... Lucius Accius

 

[Deano1976]

Wow - that simple!

Thanks

 

[Golom]

Or in SQL (since you are using 97 that lacks the replace Function)

Public Function myReplace(T As String, s As String, w As String) As String
    Dim x() As String
    x = Split(T, s)
    myReplace = Join(x, w)
End Function

Select myReplace([SomeField], " ", "") As [FieldWithoutSpaces],

 

[raskew]

Golom -

A97 also lacks the Split() function.

Bob

 

[raskew]

... and the Join() function.

Bob

 

[abaldwin]

If you find the VBE6.dll (readily available on the net I believe from MS themselves), you can use the replace, join, and split functions by putting the dll in you system32 folder and register it.

Especially helpfull to have the replace function available in 97.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[raskew]

It sounds wonderful. Let me show my ignorance here.
VBE6.dll exists on my system. It doesn't show up under
References. What to do to register it?

Best wishes, Bob

 

[straybullet]

Golom:
OK - wait a minute - I know it's been a few years since I've used 97, but you're saying that there was no Find/Replace ability in 97? That pressing Ctrl + H did nothing?

I remember using it... Time for me to go to "the home" lol

Let them hate - so long as they fear... Lucius Accius

 

[rjoubert]

straybullet...Golom was referring to the intrinsic "Replace" function in Access, not the CTRL + H functionality.

 

[rjoubert]

info on the "Replace" function...

http://www.techonthenet.com/access/functions/string/replace.php

 

[raskew]

Hey rjoubert-

That's all well and good, but it does nothing if you're using A97.

If we can't come up with a universal solution, I'll lay one on you, using 'roll your own' substitutes for each of the built-in A97+ functions.

Abaldwin's solution sounds great. Just need a littlr guidance on how to implement it.

Best wishes, Bob

 

[abaldwin]

I am trying to make sure the file name I gave you is correct. We had two instances where this dll had to be moved to a users machine system32 folder and use regsvr32 to register the dll at the OS level.

One was a AC2000 install that for some reason did not have the replace function handy. The other was an Acc97 user that needed to run the same query.

I believe the file name I gave you is the one that we used and made the functions available. I am going to try and make sure. Should be back shortly.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[straybullet]

rjoubert:
whew! Thank you - thought I was losing it!

Let them hate - so long as they fear... Lucius Accius

 

[abaldwin]

Okay, after looking around in my emails I have found the following.

1. No matter what is posted in EVERY forum on the net about not being able to do this. I am doing this.

I have in my system32 folder a dll named VBE6.dll
File Version is 6.4.99.69
Comments are June 30, 2003

2. I am definitely able to on all our machines including one with AC97 able to use the replace command (UNWRAPPED) in the following manners

SELECT cus_no, replace(item_no, "_", "") FROM CusItems;

Data
cus_no item_no returns
123456 Andys_part Andys part
232456 Your_Part Your_Part


On the following sql
Update cusItems set item_no = replace(item_no, "_", " ");

Does in fact change my fields from
Andys_part to Andys Part
your_Part to your part

There are no modules in my test db and thus there are no wrappers for the common replace function.

The only reason I got twiggy with this reply is that I remember EVERY forum on the net stated this could not be done in queries or reports and that you had to wrap the function inside your own function.

If you have the VBE6.dll in your system32 folder I would check the versions. I do not even remotely remember where I found the info that turned me onto this fix, but I had the exact problem......Huge query that utilized the REPLACE function in 100's of queries and on the production machine did not work, even though the developement machine was an image of the production machine with some extra patches. REplaced production dll with develope dll and VWALA all is well.

Check your version and let me know.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.