Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: Length and substitute

    I have a field that has data

    S-TW--S Len=4
    ---WTF- Len=3
    --T-T-- Len=2
    etc

    I need to query this to give me a "LEN" without the "-". I know how to do this in excel with the substitute function. How do I get this in access?

    texasalynn

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    debug.print Len("S-TW--S") - CharCount("S-TW--S", "-")



    Function CharCount(MyString As String, FindChar As String) As Long

    Dim lngPosition As Long
    Dim lngCharCount As Long

    lngPosition = InStr(MyString, FindChar)

    If lngPosition > 0 Then
    lngCharCount = 1
    End If

    Do While lngPosition > 0
    lngPosition = InStr(lngPosition + 1, MyString, FindChar)
    If lngPosition > 0 Then
    lngCharCount = lngCharCount + 1
    End If
    Loop

    CharCount = lngCharCount

    End Function

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    Thanks - but whe re do I put this and how do I use. Sorry I not that sophisicated. :-(

    texasalynn

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Not sure what you are trying to accomplish but this should help:

    Put the CharCount function in a module

    Assumption
    Table1 has a field called Field1 with a value of "S-TW--S"

    Query:
    Select Len([Field1]) - CharCount([Field1], "-") as FieldLength From Table1;

    Form with recordsource set to Table1
    A textbox could have Control Source set to: =Len([Field1])-CharCount([Field1],"-")

    Hope this helps - Matt

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that custom function is nice, but not really necessary

    if all you want is the length of a field after the dashes have been stripped out, just use

    select Len(Replace(fieldname,'-',''))

    see String Manipulation Functions in Access

    rudy
    http://rudy.ca/

  6. #6
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    I was told that the "REPLACE" works in 2000 and above.

    Unforunately I'm using 97

    Thanks . . .
    texasalynn

  7. #7
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    modified for replacing/stripping

    Function CharReplace charac(MyString As String, FindChar As String, ReplaceChar As String) As String

    Dim lngPosition As Long

    lngPosition = InStr(MyString, FindChar)

    Do While lngPosition > 0
    MyString = Left(MyString, lngPosition - 1) & ReplaceChar & Right(MyString, Len(MyString) - lngPosition)
    lngPosition = InStr(MyString, FindChar)
    Loop

    CharReplace = MyString

    End Function


    You could also do this by looping through the string one character and rebuilding it as you see fit. You would use the Mid$ function and a counter as the current postition. Counter would be 1 to length of the string.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Unforunately I'm using 97"

    okay, in that case the function may be the better choice

    if it's only a 7-character string, you could "hardcode" a solution right into your query as follows:

    Code:
    select Len(
      iif(mid(strng,1,1)='-','',mid(strng,1,1))
    + iif(mid(strng,2,1)='-','',mid(strng,2,1))
    + iif(mid(strng,3,1)='-','',mid(strng,3,1))
    + iif(mid(strng,4,1)='-','',mid(strng,4,1))
    + iif(mid(strng,5,1)='-','',mid(strng,5,1))
    + iif(mid(strng,6,1)='-','',mid(strng,6,1))
    + iif(mid(strng,7,1)='-','',mid(strng,7,1))
     ) as FieldLength
    from yourtable
    i wouldn't want to keep doing this if it's required in many queries, though

    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •