Using Functions (1 Viewer)

[harrisw]

I have created the function below:

Public Function StripSpace(txtField As String) As String

Dim txtTemp As String

For x = 1 To Len(txtField)

If Mid(txtField, 1, x) <> " " Then
txtTemp = txtTemp + Mid(txtField, 1, x)
End If

Next x

txtField = txtTemp

End Function


I am calling it from a query by just having stripspace(fieldname) in the top bit. How ever when I run the query this column appears blank.

Not really created functions etc before so dont really know what is wrong.

Thanks

 

[Mile-O]

The name of a function is the value to be returned.

Therefore, when you declare:

StripSpace(txtField As String) As String

you are expected to return a value into the StripSpace function. If you do not do this then you will get the default string which is a Null String (or "").

Public Function StripSpace(strField As String) As String

    Dim intCounter As Integer

    For intCounter = 1 To Len(strField)
        If Mid(txtField, intCounter, 1) <> Chr(32) Then
            StripSpace = StripSpace & Mid(strField, intCounter, 1)
        End If
    Next intCounter

End Function

.

You also had the arguments in the Mid function around the wrong way.


If you managed to get your code running without having to declare X then I would advise you to goto Tools -> Options and select Require Variable Declaration. This will put the Option Explicit statement at the top of each of your modules and is a brilliant safe guard against some instances of faulty code.

 

[harrisw]

OK

I now have the code

Public Function StripSpace(txtField As String) As String

Dim intcount As Integer

For intcount = 1 To Len(txtField)

If Mid(txtField, intcount, 1) <> Chr(32) Then
StripSpace = StripSpace & Mid(txtField, intcount, 1)
End If

next intcount

End Function

When I run the query it says undefined function "stripspace" in expression

 

[Mile-O]

Do you have the function in a standalone module or a form's module? It should be in a standalone module.

 

[harrisw]

No its in a stand alone module.

All im doing to use the function is in a query same as below.

Expr1: StripSpace(fieldname to remove spaces)

 

[harrisw]

If i take the tick out of

Require Variable Declaration

then it works ok.

Any ideas?

 

[Jon K]

It's considered good practice to require variable declaration.

_

Public Function StripSpace(txtField As String) As String

   Dim intcount As Integer
   Dim Stripped As String
  
   For intcount = 1 To Len(txtField)
      If Mid(txtField, intcount, 1) <> Chr(32) Then
         Stripped = Stripped & Mid(txtField, intcount, 1)
      End If
   Next intcount
 
   StripSpace = Stripped

End Function

In Access 2000 or above, you can use the built-in Replace() function.

Public Function StripSpace2(txtField As String) As String
  
   StripSpace2 = Replace(txtField, " ", "")

End Function