remove blanks from item number in a table

[!Rdooley]

remove blanks from item "numbers" in a table

UPdataed at 3:11PM

I have a table with item ID's based on this format "O35 1234 56" and I need to remove the spaces in between.

An update query would be the easiest I just cant get the positions right.


I read the following thread

http://www.access-programmers.co.uk/forums/showthread.php?threadid=12241&highlight=remove+blank

It almost solves my problem, But, my item is slightly different.


Need some help?

Thanks

 

[dcx693]

What version of Access are you using? If it's Access 2000 or higher, use the Replace function like this in a query:
Replace("ID O35 1234 56"," ","")

 

[Calvin]

Using the example you submitted your solution would be:

NewString=Left([Field],2) & Mid([Field],4,3) & Mid([Field],8,4) & Mid([Field],13,2)

to get "ID O35 1234 56" to look like "IDO35123456"

But dcx693's sugestion would probably be easier.

 

[!Rdooley]

Just O35 1234 05

Thanks, BTW Access 2000

 

[dcx693]

Mid(Replace("ID O35 1234 56"," ",""),3)
will get you
O35123456

 

[!Rdooley]

ID is not in the item number just O## #### ##. Plus I am dealing with several different records using the same format. So I will need to update the entire column and not just on record.

Thanks for the ideas , so far.

 

[dcx693]

Use a variation of that Mid(Replace()) function I posted in an update query to apply it to the entire column - that's if you want to permanently alter the data. If not, then just make it a calculated column in a select query.

 

[raskew]

For a generic solution that will get rid of particular characters, wherever they reside, try Chopit().

From the debug window:
? chopit("O35 1234 56", " ")
O35123456

Function ChopIt(pStr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Name:      ChopIt (Function)
'Purpose:   Remove a list of unwanted
'           characters from a string
'Inputs:    From debug window:
'           '? chopit("(626) 123 5555", ")","(")
'Output:    626 123 5555
'*******************************************

Dim strHold As String
Dim i As Integer, n As Integer

strHold = Trim(pStr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
   Do While InStr(strHold, varmyvals(n)) > 0
      i = InStr(strHold, varmyvals(n))
      strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
   Loop
Next n
ChopIt = Trim(strHold)
End Function

HTH - Bob

 

[!Rdooley]

Thanks

Use a version of the mid replace above and applied it to the entire column.

Worked great
Thanks all!