Using a function in query

[John]

Hi

I need to run a query string while stripping all the spaces from one of the
fields being returned by query. I need something like this;

st = ""SELECT ID, Replace(Tel,"" "","""") FROM MyTable"

Problem is Replace function is not available in Jet as far as I know. How
can I strip the spaces as I need?

Thanks

Regards

 

[Arvin Meyer [MVP]]

The Replace function is enabled in JET from Access, as long has you have VBA
code enabled and Sandbox security not turned on. If that's not the case, try
this code, instead:

Public Function replace(ByVal TextBlock As String, ByVal FindWhat As String,
_
Optional ByVal replaceWith As String = "") As String

Dim startPlace As Long
Dim foundAt As Long
Dim newString As String
Dim oldLen As Integer

startPlace = 1
newString = ""
CheckValues TextBlock, FindWhat, replaceWith

oldLen = Len(FindWhat)
If oldLen = 0 Then
replace = TextBlock
Exit Function
End If
Do
foundAt = InStr(startPlace, TextBlock, FindWhat, vbTextCompare)
If foundAt > 0 Then
newString = newString & Mid(TextBlock, startPlace, foundAt -
startPlace)
newString = newString & replaceWith
startPlace = foundAt + oldLen
End If
Loop Until foundAt = 0
replace = newString & Mid(TextBlock, startPlace)
End Function

 

[John W. Vinson]

Hi

I need to run a query string while stripping all the spaces from one of the
fields being returned by query. I need something like this;

st = ""SELECT ID, Replace(Tel,"" "","""") FROM MyTable"

Problem is Replace function is not available in Jet as far as I know. How
can I strip the spaces as I need?

Thanks

Regards

What version of Access are you using? Replace should work fine in AccessXP and
later. If it's not working what ARE you getting? An error message, blanks,
....?

 

[gllincoln]

Hi John,

To clean the left and right sides you can use TRIM(Tel)

For spaces within the string, that is code time stuff. As soon as I wrote a very short first version of this, I realized how many times I've had to strip double-quotes or commas or spaces, or dashes, and so forth. So I put a silly millimeter more effort into it and made a lot, a whole lot more functional function. <grin> This is a bit of overkill for your specific request but it should be rather useful to have around. I've imported it into my 'utility.bas module - the one I append to nearly every Access/Excel project I tackle during the development phase so that means I like it. <grin>

The way this function is used -

for spaces would be
= Strip([string_field_name], " ")

Or you could strip dashes
= Strip([string_field_name], "-")

Or you could clean off phone numbers
= Strip([string_field_Name], "()- ")

Or you could kill the numeric characters
= Strip([string_field_name],"0123456789")

Public Function Strip(ByVal s As String, ByVal myChars As String)

Dim x As Integer 'loop counter
Dim testLoop As Integer 'test loop counter
Dim flag As Boolean 'pass fail marker
Dim myFCount As Integer 'count of filter chars
Dim myFilter() As Integer 'array stores ascii codes of filter

'This is an optional cleanup step
'can be deleted if you prefer without harm
s = Trim(s)

'did we get a filter?
If Len(Nz(myChars)) Then

'build array of ascii filters
myFCount = Len(myChars)

'redefine the array to fit the filter
ReDim myFilter(myFCount)

'populate the filter array with ascii codes
For x = 1 To myFCount
myFilter(x) = Asc(Mid(myChars, x, 1))
Next x

'walk thru the string to be filtered
'one character at a time
For x = 1 To Len(s)

'init flag false each time
flag = False

'walk thru myfilter array testing for hits
'set flag True if one is found
For testLoop = 1 To myFCount
If InStr(Mid(s, x, 1), Chr(myFilter(testLoop))) Then
flag = True
End If
Next testLoop

'keep this character?
If Not flag Then
Strip = Strip & Mid(s, x, 1)
End If

'get the next character to test
Next x

Else
'no filter, no point in continuing
MsgBox "No filter characters received.", vbCritical, "NOTHING TO DO"
'send back the received string untouched
'obey the rule about doing no harm
Strip = s
Exit Function
End If

End Function


Hope this helps....
Gordon