Hello All -
I am a volunteer with my area Girl Scouts. We have access to a website where we can download up to date membership information into an Excel spreadsheet. However, the data formats are horrible ... phone numbers sometimes have a space after the area code and sometimes don't ... the grades are put a "0" in front of "K" so it will sort before "1" ... and, my current problem - the troop numbers are text AND aren't always actual troop numbers -- "New Troop forming at Center Elem." is one example. My database application has the troop numbers as numbers.
I have written custom functions to clean up all the fields. They all work except my troop number function. What the heck am I doing wrong???
My function:
Code:
Public Function clnTroop(rawTroop As String) As Integer
If (Left([rawTroop], 5)) <> "Troop" Then
clntroop = Null
Else
clnTroop = CLng(Right([rawTroop], 5))
Debug.Print "Troop # = " & clnTroop
End If
End Function
It's used in a subroutine like so:
Code:
'A bunch of Dim statements for things that aren't troop numbers
Dim cleanTroop As Integer
Dim rawTroop As String
'A bunch of other functions for fields
'These functions work fine
rawTroop = DLookup("[Troop/Group]", strTable, "NoName = " & i) '"NoName" is a field that didn't have a header in Excel. It's row numbers. "i" is the row number for a particular record
cleanTroop = clnTroop(rawTroop)
rst!TrpNum = cleanTroop
'several more functions for fields
'these functions also work fine
When I use the immediate window and enter clnTroop("Troop 01234"), I get "Troop # = 1234" (without quotes).
When I enter clnTroop("New Troop forming at Center Elem."), I get nothing. Shouldn't I at least get "Troop # =" ?
Thank you!
Susie
Kansas