Remove Trailing Spaces

[newbie123 via AccessMonster.com]

Hi!

I read through the various posts on this site about the TRIM function. I
have two tables that i would like to compare. The common field between the
two is the EmpID.

One file does not have spaces after the ID#. The other file does. In the
second file I did the following:

EmpID2: TRIM(EmpID)

This apparently does not work - the space is still there.

Now in reading the posts, there is mention of that space not really being a
space, but maybe a carriage return, etc. If that is the case, is there
anything else I can do to get rid of the space?

Thanks!

 

[Ken Snell [MVP]]

Let's find out what character(s) is/are at the end of the string.

Add these fields to a query that is based on the table that has the "spaces"
at the end of the EmpID field:

EmpID
LastChar_Asc: Asc(Right(EmpID, 1))
LastChar_Chr: Chr(Asc(Right(EmpID, 1)))
SecondLastChar_Asc: Asc(Right(Right(EmpID, 2), 1))
SecondLastChar_Chr: Chr(Asc(Right(Right(EmpID, 2), 1)))
ThirdLastChar_Asc: Asc(Right(Right(EmpID, 3), 1))
ThirdLastChar_Chr: Chr(Asc(Right(Right(EmpID, 3), 1)))

Run the query and inspect the output.

When one of the above "_Chr" fields matches the last "significant" character
in EmpID, then we've identified how many "trailing" characters there are,
and which ones they are.

Post the results here and we can advise on how to use the Replace function
to remove the "trailing" character(s).

 

[newbie123 via AccessMonster.com]

Here are the first 10 records results. Where there # is 32 below, there are
some records that have

LastChar_Asc LastChar_Chr SecondLastChar_Asc SecondLastChar_Chr
ThirdLastChar_Asc ThirdLastChar_Chr
50 2 50 2 50 2
65 A 65 A 65 A
160   160   160  
32 32 32
160   160   160  
52 4 52 4 52 4
32 32 32
32 32 32
32 32 32
32 32 32

Please let me know if i need to resubmit this in a "cleaner" format. The
width of the box wraps the data.

Let's find out what character(s) is/are at the end of the string.

Add these fields to a query that is based on the table that has the "spaces"
at the end of the EmpID field:

EmpID
LastChar_Asc: Asc(Right(EmpID, 1))
LastChar_Chr: Chr(Asc(Right(EmpID, 1)))
SecondLastChar_Asc: Asc(Right(Right(EmpID, 2), 1))
SecondLastChar_Chr: Chr(Asc(Right(Right(EmpID, 2), 1)))
ThirdLastChar_Asc: Asc(Right(Right(EmpID, 3), 1))
ThirdLastChar_Chr: Chr(Asc(Right(Right(EmpID, 3), 1)))

Run the query and inspect the output.

When one of the above "_Chr" fields matches the last "significant" character
in EmpID, then we've identified how many "trailing" characters there are,
and which ones they are.

Post the results here and we can advise on how to use the Replace function
to remove the "trailing" character(s).

Hi!

[quoted text clipped - 16 lines]

Thanks!

 

[newbie123 via AccessMonster.com]

I got this to work...assistance is no longer needed.

Thanks!!!

Here are the first 10 records results. Where there # is 32 below, there are
some records that have

LastChar_Asc LastChar_Chr SecondLastChar_Asc SecondLastChar_Chr
ThirdLastChar_Asc ThirdLastChar_Chr
50 2 50 2 50 2
65 A 65 A 65 A
160   160   160  
32 32 32
160   160   160  
52 4 52 4 52 4
32 32 32
32 32 32
32 32 32
32 32 32

Please let me know if i need to resubmit this in a "cleaner" format. The
width of the box wraps the data.

Let's find out what character(s) is/are at the end of the string.

[quoted text clipped - 23 lines]

 

[Ken Snell [MVP]]

My bad... gave you wrong field expressions:

EmpID
LastChar_Asc: Asc(Right(EmpID, 1))
LastChar_Chr: Chr(Asc(Right(EmpID, 1)))
SecondLastChar_Asc: Asc(Left(Right(EmpID, 2), 1))
SecondLastChar_Chr: Chr(Asc(Left(Right(EmpID, 2), 1)))
ThirdLastChar_Asc: Asc(Right(Left(EmpID, 3), 1))
ThirdLastChar_Chr: Chr(Asc(Left(Right(EmpID, 3), 1)))

I see from your other post that you have solved your problem. I'm
curious...what did you find, and what did you do to fix it?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Let's find out what character(s) is/are at the end of the string.

Add these fields to a query that is based on the table that has the
"spaces" at the end of the EmpID field:

EmpID
LastChar_Asc: Asc(Right(EmpID, 1))
LastChar_Chr: Chr(Asc(Right(EmpID, 1)))
SecondLastChar_Asc: Asc(Right(Right(EmpID, 2), 1))
SecondLastChar_Chr: Chr(Asc(Right(Right(EmpID, 2), 1)))
ThirdLastChar_Asc: Asc(Right(Right(EmpID, 3), 1))
ThirdLastChar_Chr: Chr(Asc(Right(Right(EmpID, 3), 1)))

Run the query and inspect the output.

When one of the above "_Chr" fields matches the last "significant"
character in EmpID, then we've identified how many "trailing" characters
there are, and which ones they are.

Post the results here and we can advise on how to use the Replace function
to remove the "trailing" character(s).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[newbie123 via AccessMonster.com]

I found this code on another forum:

Option Compare Database
Option Explicit

Private Declare Function IsCharAlphaNumeric Lib "user32" Alias
"IsCharAlphaNumericA" (ByVal cChar As Byte) As Long

Function fReturnAlphaNumAPI(strPassed As String)
Dim intI As Integer
For intI = 1 To Len(strPassed)
If IsCharAlphaNumeric(Asc(Mid(strPassed, intI, 1))) = 1 Then
fReturnAlphaNumAPI = fReturnAlphaNumAPI & Mid(strPassed, intI, 1)
End If
Next
End Function

My bad... gave you wrong field expressions:

EmpID
LastChar_Asc: Asc(Right(EmpID, 1))
LastChar_Chr: Chr(Asc(Right(EmpID, 1)))
SecondLastChar_Asc: Asc(Left(Right(EmpID, 2), 1))
SecondLastChar_Chr: Chr(Asc(Left(Right(EmpID, 2), 1)))
ThirdLastChar_Asc: Asc(Right(Left(EmpID, 3), 1))
ThirdLastChar_Chr: Chr(Asc(Left(Right(EmpID, 3), 1)))

I see from your other post that you have solved your problem. I'm
curious...what did you find, and what did you do to fix it?

Let's find out what character(s) is/are at the end of the string.

[quoted text clipped - 39 lines]