Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Testing for space (' ') in a string in a query (2000)

    The last two characters in some records in my table are digits (00, 01 ...etc), preceded by a space, while in others they are part of complete words. I want to be able to remove the digits - can I do this in a query?

    I have tried jnl/conf: IIf(Left([Meeting],Len([Meeting])-3)=" ","OK",[Meeting]) - this leaves the records I don't want to change intact, but also does not return 'OK' for those I do want to change.

    What am I doing wrong?

    Thanks!

    Kiwi44

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    The expression Left([Meeting],Len([Meeting])-3) returns all but the last 3 characters of the value of Meeting. For instance, if Meeting is "Kiwi 44", the expression will return "Kiwi". Replace this part of the expression by

    Mid([Meeting],Len([Meeting])-2,1)

    This returns the character 2 positions before the end of the string.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    Yuo say that you want to remove the digits at the end of the string.
    Try this:
    iif(IsNumeric(Right(Meeting,2)), Left(Meeting,Len(Meeting)-2), Meeting)

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    To add to other comments, you can test logic involved by creating user-defined function like this example:

    Public Function TestText(ByRef strTxt As String) As Boolean

    TestText = (Asc(Mid(strTxt, Len(strTxt) - 2)) = 32) And IsNumeric(Right(strTxt, 2))

    End Function

    Note that Asc function evaluates only first character of a string, so you can use Mid without specifying length. Asc code 32 is a space. Sample results:

    ? TestText("ABC 12")
    True
    ? TestText("ABC12")
    False
    ? TestText("ABC DE")
    False

    The same logic can be used in query expression using IIF function. Not sure if you just want "OK" if condition equates to True, or if you want the space and digits truncated. Here is example of use in query for both possibilities:

    SELECT Field1, IIf((Asc(Mid([Field1],Len([Field1])-2))=32) And IsNumeric(Right([Field1],2)),"OK",[Field1]) AS Expr1, IIf((Asc(Mid([Field1],Len([Field1])-2))=32) And IsNumeric(Right([Field1],2)),Left([Field1],Len([Field1])-3),[Field1]) AS Expr2
    FROM Table1;

    If testing this replace Field1, Table1, etc with actual names. The idea of using function is to have simple way to test results in debug window. If correct results are obtained, you can then copy logic to query expression, replacing variable names with field names.

    HTH

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    I have a situation where the last 8 characters of a string are;
    Space-SpaceBlend

    How do I test if " - Blend" exists, if so remove it ?

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    That should not be too hard, in a query you can use this IIF expression:

    SELECT Field2, IIf(Right([Field2],8)=" - Blend",Left([Field2],Len([Field2])-8),[Field2]) AS Expr1
    FROM Table2

    In above example if Field2 ends in " - Blend" then last 8 characters will be removed by subtracting 8 from length of field and using result with Left function. In test results, "ABC - BLEND" resulted in "ABC", "XYZ - BLEND" resulted in "XYZ", etc. If field did not end with " - Blend" it remains as is.

    HTH

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    I got this function from Helen Feddema's site. Her function stripped out non alpha numeric but I added 1 to 0 to strip out numbers too.
    Within the VBA see strStripChars for the characters it removes. You can amend this for the characters you chose.
    I recommend Helen's site . It has some great code which you can play with to suit your needs.
    The code I amended is as follows:
    Function StripNonAlpha(strText As String) As String
    Dim strTestChar As String, _
    lngFound As Long, _
    i As Integer, _
    strStripChars As String, _
    strTestString As String

    strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?1234567890" & Chr$(34) & Chr$(13) & Chr$(10)
    strTestString = strText
    i = 1
    Do While i <= Len(strTestString)
    strTestChar = Mid$(strTestString, i, 1)
    lngFound = InStr(strStripChars, strTestChar)
    If lngFound > 0 Then
    strTestString = Left(strTestString, i - 1) & Mid(strTestString, i + 1)
    Else
    i = i + 1
    End If
    Loop
    StripNonAlpha = strTestString
    End Function

    Hope this works for you.
    Peter

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    Thanks for the updates,I'll have study them.
    I will look to use VBA rather than in a query, it shouldn't be too difficult to convert.
    Thanks again.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    That should not be hard, for example you can use a function to convert text if necessary:

    Public Function ChangeText(ByVal strTxt As String) As String

    ' Assumes only string will be passed to function (use Nz):

    If Right$(strTxt, 8) = " - Blend" Then
    ChangeText = Left$(strTxt, Len(strTxt) - 8)
    Else
    ChangeText = strTxt
    End If

    End Function

    Sample use:

    ? ChangeText("ABC - Blend")
    ABC
    ? ChangeText("ABC -Blend")
    ABC -Blend
    ? ChangeText("ABC")
    ABC

    Note if using "$" version of string functions, make sure strTxt is a valid string, not Null, or error will occur. For this type of thing I usually use Nz when calling function to ensure only valid string is passed to function. (Passing an empty string in this case will simply return another empty string.)

    HTH

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    The function you posted works, but I think it's doing things the hard way. I'd simplify it to something like this:

    Public Function GetAlphaText(ByVal strTxt As String) As String

    Dim n As Long
    Dim b() As Byte

    ' Convert from Unicode to byte array of ANSI character codes:
    b = StrConv(strTxt, vbFromUnicode)

    For n = 0 To UBound([img]/forums/images/smilies/cool.gif[/img]
    Select Case b(n)
    Case 0 To 64, 91 To 96, 123 To 255
    b(n) = 0
    End Select
    Next

    'Convert back to Unicode string:
    GetAlphaText = Replace(StrConv(b, vbUnicode), Chr$(0), vbNullString)
    Erase b

    End Function

    The above example takes advantage of VB StrConv function which (efficiently) converts text to and from Unicode (same function is used to convert text to Upper, Lower, or Proper case). (At some point, the text characters have to be converted to numbers, so why not do it directly?) The Select Case statement excludes standard alphabetic characters from A to Z (ASCII 65 to 90) and a to z (ASCII 97 to 122) and can be modified as necessary to include/exclude other characters (numbers 0 to 9 are ASCII 48 to 57). As noted in a recent post, string concatenation in a loop (such as used in StripNonAlpha function) can be inefficient due to the way VB handles strings internally. For example, I ran some quick timed tests running both functions thru a loop 10,000 times (in a varying sequence) with a simple text string:

    ? StripNonAlpha("ABC 123 @#$ xyz")
    ABCxyz

    ? GetAlphaText("ABC 123 @#$ xyz")
    ABCxyz

    As seen both functions return identical results. Using the Windows GetTickCount API function to measure time elapsed in milliseconds, on average the StripNonAlpha function took over 500 milliseconds to execute 10,000 times, while the GetAlphaText function took appx 250 milliseconds, or 50% faster. Of course results may vary in any test; it's possible that my computer has some inherent bias for "my" function over the other one....

    You can use StrConv function in similar way to create your own "custom" case conversion functions. For example, if you have some unusual need to invert the case of a text string:

    Public Function InvertCase(ByVal strText As String) As String

    Dim n As Long
    Dim b() As Byte
    Const SHIFT_CASE = &H20

    b = StrConv(strText, vbFromUnicode)

    For n = 0 To UBound([img]/forums/images/smilies/cool.gif[/img]
    Select Case b(n)
    Case 65 To 90
    b(n) = b(n) + SHIFT_CASE
    Case 97 To 122
    b(n) = b(n) - SHIFT_CASE
    End Select
    Next

    'Convert back to Unicode:
    InvertCase = StrConv(b, vbUnicode)
    Erase b

    End Function

    Example:
    ? InvertCase("ABC 123 @#$ xyz")
    abc 123 @#$ XYZ

    HTH

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    Thanks Mark

    I've not had chance yet to implement it, but I'm sure it will be ok.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    I've put the function in the forms module and in a stand alone module in two variations:

    <pre>Public Function ChangeText(ByVal Item As String) As String
    If Right(Item, 8) = " - Blend" Then
    ChangeText = Left(Item, Len(Item) - 8)
    Else
    ChangeText = Item
    End If
    End Function</pre>


    And

    <pre>Public Function ChangeText(ByVal Item As String) As String
    If Right$(Item, 8) = " - Blend" Then
    ChangeText = Left$(Item, Len(Item) - 8)
    Else
    ChangeText = Item
    End If
    End Function</pre>


    In the procedure to call it, ie After_Update, I've enterred "ChangeText" without the quotes.
    In both occasions I'm getting "Argument Not Optional"
    I've probably missed something simple.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    The error message tells you that you have to supply an argument to the function; moreover, you have to do something with the result. The instruction should look like

    Field1 = ChangeText(Field1)

    where Field1 is the field that contains the text to be changed.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    Hans
    Thanks, I thought It would be simple.
    Unfortunately, the help files do tend to not be helpful.

  15. #15
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing for space (' ') in a string in a query (2000)

    To all who responded - thanks for the useful suggestions that I will add to my files for future reference. For now, I will use Pat's response.

    Kiwi44

Posting Permissions

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