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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks Mark

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

Reply | Quote

Thanks Mark

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

Reply | Quote

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

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

And

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

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

And

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

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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 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

Reply | Quote

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(
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(
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

Reply | Quote

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(
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(
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

Reply | Quote

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 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

Reply | Quote