Removing All Spaces Between Data In Text Field ?

[The Brown Growler]

Hi,

Would anyone please be able to help me with a query to remove all spaces between data stored in a text field?

For example:
08 CG 52 should convert to 08CG52
07 FR SR 56 should convert to 07FRSR56

Ther is no uniformity to the data, ie, there could be 2, 3, 4 or more blocks of info in the field and I need to remove all spaces and any other invisible non printing characters etc.

I have tried TRIM but it leaves the in between spaces. If there is a custom public function that I could use in the query grid then that would be ideal.

Thx & Regards
Growler

 

[stopher]

Take a look at the REPLACE function.
e.g. the following will replace all spaces with an empyt string

REPLACE([myString]," ","")

hth
Chris

 

[raskew]

Hi -

I've modified a function originally written in A97 (no Replace() function) to remove excess spaces. Copy/paste to a standard module and call as shown in the example.

Function NoSpace(pstr As String) As String

'*******************************************
'Purpose:   Removes all spaces from a string
'Coded by:  raskew
'Input:     ? nospace("07 FR SR 56")
'Output:    "07FRSR56"
'*******************************************

Dim strHold As String
    strHold = RTrim(pstr)
    Do While InStr(strHold, " ") > 0
      strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
    Loop
    NoSpace = Trim(strHold)
    
End Function

HTH - Bob

 

[raskew]

Chris -

Obviously I needed to study-up on the Replace() function. Just realized it, by default, replaces all instances of the specified value. Duh!

My solution, although it works, is totally cumbersome by comparison.

Best Wishes - Bob

 

[The Brown Growler]

Thanks for the replies and the solutions.

Rgds
Growler

 

[stopher]

Obviously I needed to study-up on the Replace() function. Just realized it, by default, replaces all instances of the specified value. Duh!

My solution, although it works, is totally cumbersome by comparison.

Don't worry Bob. I only recently picked up on it when Lagbolt posted that incredibly short code. I think you also wrote a nice bit of code if I remember rightly.

Chris

 

[gemma-the-husky]

i have the same problem

I am so used to A97, I tend to not use later functions like

replace, instrrev, and split

and use workrounds for them, when they are needed - there are probably loads of other functions that appeared after A97 as well

 

[datAdrenaline]

In A97 .. I have used these VBA6 replacements in VBA5 ...

http://www.tek-tips.com/faqs.cfm?fid=4342

I really like the way they are set up to be conditionally compiled (ie: they won't compile in a VBA6 application ... so when you upsize you will not have your native functions "overloaded")

 

[raskew]

Brent -

Suspect that most of us A97 'dinosaurs' probably have a similar set of workarounds. Just went back and checked and found similar workarounds for all of the functions listed in your article.

Best Wishes - Bob

 

[datAdrenaline]

Hey Bob ...

Yep, I had a couple (InStrREV and Replace) .. but then I found that article and just pulled them all into my A97 apps and discarded all mine for those found in the article....

BTW: the article is not mine ... it sorta sounded like you thought is was, so I thought I would clarify that! ...