Delete Carriage return query

[Mad_Mick]

Hi all, First time post, hope someone can help.

I have had to develop a contact database for about 10-15 users tha
currently have approx 2000 - 3000 contacts each.

I have built an Access DB and imported some of the existing data, bu
the original format of data that I need to import is Excel and whe
imported into the Access 97 table, it retains the carriage return
(from Excel) and represents them as a square (In the Access table).
need to replace the carriage return with a comma or a space. Is ther
any way that I can make a query that will remove or replace a carriag
return??

Sample data: OPTILU2008TU

I have tried to simply search for UniCode Alt+0129, but no good

 

[Nikos Yannacopoulos]

Mick,

I think you're focusing on solving the wrong problem here... Your real
problem is a bad import, or you wouldn't get tabs and stuff (more likely
than carriage returns). My suggestion is you try to get the import right,
either by (a) linking the Excel sheet and running an append query, or (b)
simply opening both the Excel sheet and the Access table and doing a simple
copy-paste. This should do it nicely.

HTH,
Nikos

 

[___]

I think 'this' (http://support.microsoft.com/?kbid=316975) should hel
you.

HT

 

[Mad_Mick]

Hi Nikos,

Thanks for the suggestion, but teven a cut and paste still
retains the carriage return. The problem that I have is
that in the Access table it is represented as uni code and
I can not represent/recreate it (the carriage return ) in
a query or even a simple search and replace function.

I thought that there may be some way to construct an
update query to deconstruct the fields that are affected
and concatinate them again without the carriage return.

note: I used the Access import function, but have not
tried an update query, presumably Access would use an sql
query based on the import wizard responses anyway. There
are no tabs in the original Excel spreedsheet, just
carriage returns.

Any further suggestions would be greatly appreciated.

Mad_Mick

 

[Mad_Mick]

Thanks for the suggestion HTH, I have taken a look at the code on tha
one, but unfortunately it is only for removing spaces, I did try i
thinking that I could modify it to remove the carriage returns, but
only have Access 97 and some of the commands are for 2000 so they don'
work in 97. I did find another artical on carriage returns, but it wa
not to remove them, only to ensure the data was displayed on the nex
line rather than a single line with the carriage returns represented a
.

I am not sure that my VBA skills are upto the level required to modif
the code provided to do exactly what I need, I might have to do som
trial and error testing. I did find that the carriage return i
represented by [Chr(13)]?? in VB, but I am not sure how that helps me.

If anyone has VBA code or a query equivilant or any other ideas on ho
to remove carriage returns from an Access table or an Excel spreadshee
it would be hugely appreciated.

MAD_MIC

 

[Nikos Yannacopoulos]

Mick,

Here's your code. Just make sure to:
(a) in the Set rst line, change "Countries" to the name of your table, and
(b) install Microsoft DAO 3.51 Object Library in your database, if you don't
already have it (from within VBA editor, Tools > References).

Sub remove_carriage_returns()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Countries")
rst.MoveFirst
Do Until rst.EOF
rst.Edit
For i = 0 To rst.Fields.Count - 1
rst.Fields(i) = Replace(rst.Fields(i), Chr(13), "")
Next
rst.Update
rst.MoveNext
Loop
rst.Close
End Sub

HTH,
Nikos

 

[Guest]

hi Nikos, Had same problem as Mad Mick. tried your suggestion, copying and pasting, but when running the module I get "run time error '94', invalid use of null". what am I doing wrong


----- Nikos Yannacopoulos wrote: ----

Mick

Here's your code. Just make sure to
(a) in the Set rst line, change "Countries" to the name of your table, an
(b) install Microsoft DAO 3.51 Object Library in your database, if you don'
already have it (from within VBA editor, Tools > References)

Sub remove_carriage_returns(
Dim db As DAO.Databas
Dim rst As DAO.Recordse
Set db = CurrentDb(
Set rst = db.OpenRecordset("Countries"
rst.MoveFirs
Do Until rst.EO
rst.Edi
For i = 0 To rst.Fields.Count -
rst.Fields(i) = Replace(rst.Fields(i), Chr(13), ""
Nex
rst.Updat
rst.MoveNex
Loo
rst.Clos
End Su

HTH
Niko

Thanks for the suggestion HTH, I have taken a look at the code on tha

one, but unfortunately it is only for removing spaces, I did try i
thinking that I could modify it to remove the carriage returns, but
only have Access 97 and some of the commands are for 2000 so they don'
work in 97. I did find another artical on carriage returns, but it wa
not to remove them, only to ensure the data was displayed on the nex
line rather than a single line with the carriage returns represented a
Â

I am not sure that my VBA skills are upto the level required to modif

the code provided to do exactly what I need, I might have to do som
trial and error testing. I did find that the carriage return i
represented by [Chr(13)]?? in VB, but I am not sure how that helps me

If anyone has VBA code or a query equivilant or any other ideas on ho

to remove carriage returns from an Access table or an Excel spreadshee
it would be hugely appreciated

MAD_MIC