Replace spaces with tabs

[Cloonalt]

I'm trying to clean up a data file. Is there a way in code to replace spaces with tabs?

Thanks for any help.

 

[PHV]

What are the rules ?
What have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cloonalt]

I took a stab with the below, which I found here.

But I need to look at every field, so I know I'm off.

Function PrintableString()

Dim db As DAO.Database
Dim strsql As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim b As String


Set db = CurrentDb

strsql = "Select * from table2"

Set rs = db.OpenRecordset(strsql)

With rs

For i = 1 To Len(s)
b = Mid(s, i, 1)
Select Case Asc(b)
Case 32 To 126
PrintableString = PrintableString & b
End Select
Next
End With

End Function

 

[CautionMP]

Cloonalt,
Your pretty close, maybe something like this?

Function PrintableString()
Dim db As DAO.Database
Dim strsql As String
Dim rs As DAO.Recordset
[b]Dim lngField As Long[/b]

Set db = CurrentDb

strsql = "Select * from table2"

Set rs = db.OpenRecordset(strsql)

[b]Do
  For lngField = 0 To rs.Fields.Count - 1
    rs.Fields(lngField) = Replace(rs.Fields(lngField), " ", vbTab)
  Next lngField
  rs.MoveNext
Loop Until rs.EOF[/b]

rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cloonalt]

I get error

Update or CancelUpdate without Add New or Edit

Runtime error 3020

Thanks for your help

 

[CautionMP]

Coonalt,
Sorry, I'm not firing on all cylinders today.

...
Do
  [b]rs.Edit[/b]
  For lngField = 0 To rs.Fields.Count - 1
    rs.Fields(lngField) = Replace(rs.Fields(lngField), " ", vbTab)
  Next lngField
  [b]rs.Update[/b]
  rs.MoveNext
Loop Until rs.EOF

CMP

 

[Cloonalt]

Great, that worked. Now how do I handle nulls?

Thanks!!

 

[CautionMP]

Cloonalt,
You can use [tt]Nz()[/tt] to change the [tt]NULL[/tt]s to something else (like the [tt]Replace()[/tt] function) or use [tt]IsNull()[/tt] as part of a decision structure for more extensive replacements.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)