Removing symbols? 1

[AppStaff]

I have box that's produced in a report as a result of unseen characters in a field on my datasource.

?

I use ODBC on an oracle database to retrieve data to an MS Access 2002 database. My report is in Excel 2002 and uses MS Query to select data from my Access DB.

I'd like to program a way to get rid of these boxes but I haven't found a way to identify them yet. I've tried manually searching and replacing to determine what character this is exactly and I tried some code written by MS to do it.

http://support.microsoft.com/?kbid=210433

Nothing has been successful thus far.

Has anyone experienced this and developed a procedure for this? Currently I'm manually modifying this which is quite painful. Thanks for any responses.

 

[Lilliabeth]

Maybe you are saying you tried this, but can you select and copy the character and paste it in the find part of the Find and Replace dialog box?

 

[gizmo1973]

or try the key above the Tab key to the left of the 1 key in F&R if you haven't, it's usually my culprit for strange symbols that won't go away!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[AppStaff]

Thanks for responding. I tried copy/paste and it doesn't recognize the symbol. I also used the character editor to find what it looked like to me and it didnt work either.

I believe its a carriage return character or line break or something to that effect that just doesn't convert properly. I don't see it in my access database but I do see the extra space there. I see it after the ms query returns my results.

I tried using trim on the field and sql to eliminate space and that didnt work either. It just reproduced what was already there.

 

[gizmo1973]

Sounds daft but try running text to columns on it, this sometimes clears out un-wanteds!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[AnotherHiggins]

Hi again, AppStaff ,

If these are, indeed, carriage returns, then this will work:

[ul][li]Go to Find and Replace[/li]
[li]for the "find" hold down <Alt> and type 0 1 0 on the NumPad (not the numbers at the top of the keyboard) then release <Alt>
note: you won't see anything appear in the "find" box - that's OK[/li]
[li]Leave the "replace with" box empty[/li][/ul]That's it!


Was that of any help?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AppStaff]

John, unfortunately no. It tells me excel cannot find a match.

Phil, YES! Actually that does work. I'm not sure if there are hidden consequences yet and I'll need to research automation from access.

Thanks. Star awarded.

 

[gizmo1973]

AppStaff, Which one worked?

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[AppStaff]

Your's worked Phil. I replied to you both by name in my prior post. Thanks! Any idea on how to automate this from access to excel?

 

[AppStaff]

Oops I'm sorry I realize now you meant which one of your suggestions worked Phil.

Apparently the text to columns works. I've never used that feature though so I'd like to research what exacltly its doing when I use it. I'll also need to determine how to automate this if I can from access.

 

[KenWright]

I'd pop this in your personal.xls and then just run it against your report - Cleans up a lot of garbage characters when importing from other apps / the web.

Sub a_TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
      Dim Cell As Range
      'Also Treat CHR 0160, as a space (CHR 032)
       Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
      'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
      For Each Cell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
         Cell.Value = Application.Trim(Cell.Value)
      Next Cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[AppStaff]

Thanks Ken! I'll test this out and see how it works.

 

[KenWright]

I use it almost every day and wouldn't be without it.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------