Spaces in Excel Data

[Don K]

Information from a number of fields on a web page is saved in an Excel
Spreadsheet. This spreadsheet is then imported in an Access 2003 table using
the DoCmd.TransferSpreadsheet method. All the fields in the spreadsheet are
text fields, and all of them have a trailing space after the text. I have
tried to strip the trailing spaces using the trim function as I need to
match some of these text fields to existing text in other tables. However I
am finding that the two fields are not matching, most likely due to the
trailing spaces. I have seen posts that mention an Access bug in the trim
function when a reference is missing, but I do not have that issue. I have
also tried searching at the end of the string for " " or chr(32), and
stripping it that way, but neither try will strip the trailing space. This
seems trivial, what am I missing? Is there an easier way?

 

[Ken Snell \(MVP\)]

How are you trying to use the Trim function -- in an update query? in a
select query? More details, please.

 

[Don K]

Ken, I have imported the excel data into table 1. I then have an append
query that converts the text fields into numbers and dates into table 2.
However the append query will not convert "Yes " and "No " into a field with
a Boolean data type, even using Trim. (Data Type Conversion Failure.) I am
now using a code module with recordsets with the following code:

If Trim(rs8!Member) = "Yes" Then
rs1!boolMember = True
Else
rs1!boolMember = False
End If
However it too does not trim the trailing space in "Yes ". What am I
missing?

 

[Ken Snell \(MVP\)]

Assuming that the "table 1" table holds the value as text, I am surprised by
the result unless there are extra, nondisplayable characters in the field's
value. Try this:

If InStr(1, rs8!Member, "Yes", vbTextCompare) > 0 Then
rs1!boolMember = True
Else
rs1!boolMember = False
End If

 

[Don K]

Ken, thank you for the code snippet. I will try it out. However, I still
have the issue with trying to match two text strings, the one from Excel
having the extra space at the end. Any thoughts?

 

[John Nurick]

Like Ken I wonder if there's something other than a space at the end of
the strings from Excel. If you have the string in
rs8!Member
what is the value of
Asc(Right(rs8!Member, 1))
and
AscW(Right(rs8!Member, 1))
? If it's anything other than 32, Trim() won't have removed it.

 

[Don K]

John and Ken, thanks for your help. John, when I try your code, the result
is 160. What is that?

 

[Ken Snell \(MVP\)]

160 is the ASCII value of an accented small letter a (accent mark goes right
to left as you go from top to bottom of accent mark). Not sure that "text"
posting will show the actual character but I've pasted one from Word
document below:

á




--

Ken Snell
<MS ACCESS MVP>

 

[John Nurick]

Up to a point, Ken. In modern encodings such as Unicode and the 8-bit
"Windows (Western)" character set, 160 is the position of the no-break
space, which figures.

160 maps to lower case e-acute in the original DOS character set (code
page 437, what Microsoft now calls OEM).

 

[Don K]

John, it makes sense that it is a space, just a non-breaking one. So to
remove it, I could do:
If Asc(Right(FieldName),1)=160 Then
FieldName =Left(FieldName(Len(FieldName -1)))
End If
Is that correct?

 

[Ken Snell \(MVP\)]

Thanks, John. Have never had a good grasp of all these character sets....
--

Ken Snell
<MS ACCESS MVP>

 

[MH]

Alternatively:

If Left(rs8!Member, 1) = "Y" Then
rs1!boolMember = True
Else
rs1!boolMember = False
End If

MH

John, it makes sense that it is a space, just a non-breaking one. So to
remove it, I could do:
If Asc(Right(FieldName),1)=160 Then
FieldName =Left(FieldName(Len(FieldName -1)))
End If
Is that correct?