Taking out spaces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am new to access. I have a table that has a field of data type text. The
data in this field has spaces embedded and I want to take out the spaces. For
example, the data shows up as "4F- 16B -K- 12" and I want it to look like
"4F-16B-K-12". Can anyone help me? Thanks in advance.
 
Sharman,

Assuming you are using Access 2000 SR3 or later, run an Update Query to
update YourField to...
Replace([YourField]," ","")

Please post back if you need more specific help with this.
 
Old fassioned loop with and if statement. I did not test it. Important is
that in the for next loop Llen = 1,2,3 etc when a space is found by the if
on 3 Mid(xx,1,3) & trim(mid(x,4,100)) Llen -1 3 -1 = 2 and Llen +1 =
3+1 = 4 . The space is no longer a part of your string. len(field_Spaces)

[Form_DDdata]![Field_Spaces]

Private Sub FixSpaces
Dim Llen as long

if mid(me.Field_Spaces,llen,1) = " " then
me.Field_Spaces = mid(me.Field_Spaces,1,Llen -1) &
mid(me.Field_Spaces,Llen +1,len(me.Field_Spaces)

end if
next Llen
Emd Sib

'Wroking Example.
Private Sub Command1_Click()
Dim Llen As Long
Dim Ttemp As String
Ttemp = "123 56789"
For Llen = 1 To 10
If Mid(Ttemp, Llen, 1) = " " Then
Ttemp = Mid(Ttemp, 1, Llen - 1) & Mid(Ttemp, Llen + 1, 10)
End If
Next Llen

MsgBox Ttemp, vbInformation, "Spaces?"
End Sub
 
Thanks a lot, Steve. It worked, although initially I got an error that the
Replace function is not defined, but I was able to find a way to bypass the
error.

Steve Schapel said:
Sharman,

Assuming you are using Access 2000 SR3 or later, run an Update Query to
update YourField to...
Replace([YourField]," ","")

Please post back if you need more specific help with this.

--
Steve Schapel, Microsoft Access MVP

Hi,

I am new to access. I have a table that has a field of data type text. The
data in this field has spaces embedded and I want to take out the spaces. For
example, the data shows up as "4F- 16B -K- 12" and I want it to look like
"4F-16B-K-12". Can anyone help me? Thanks in advance.
Click to expand...
Click to expand...
 
Thanks a lot. I tried the way you suggested and it's working.

SacCourt said:
Old fassioned loop with and if statement. I did not test it. Important is
that in the for next loop Llen = 1,2,3 etc when a space is found by the if
on 3 Mid(xx,1,3) & trim(mid(x,4,100)) Llen -1 3 -1 = 2 and Llen +1 =
3+1 = 4 . The space is no longer a part of your string. len(field_Spaces)

[Form_DDdata]![Field_Spaces]

Private Sub FixSpaces
Dim Llen as long

if mid(me.Field_Spaces,llen,1) = " " then
me.Field_Spaces = mid(me.Field_Spaces,1,Llen -1) &
mid(me.Field_Spaces,Llen +1,len(me.Field_Spaces)

end if
next Llen
Emd Sib

'Wroking Example.
Private Sub Command1_Click()
Dim Llen As Long
Dim Ttemp As String
Ttemp = "123 56789"
For Llen = 1 To 10
If Mid(Ttemp, Llen, 1) = " " Then
Ttemp = Mid(Ttemp, 1, Llen - 1) & Mid(Ttemp, Llen + 1, 10)
End If
Next Llen

MsgBox Ttemp, vbInformation, "Spaces?"
End Sub

sharman said:
Hi,

I am new to access. I have a table that has a field of data type text. The
data in this field has spaces embedded and I want to take out the spaces. For
example, the data shows up as "4F- 16B -K- 12" and I want it to look like
"4F-16B-K-12". Can anyone help me? Thanks in advance.
Click to expand...
Click to expand...
 
Sharman,

As I mentioned, the Replace function is not available in veriosn of
Access prior to Access 2000 SR3.
 
Back
Top