Update Query expression

[Guest]

I am trying to update some data, I have a field NSN which has data as follow
1111-22-333-4444 and I want to change this data to read 223334444, so I want
to be able to remove the first 4 caracters and the dashes. I,m using
Microsoft Access 2000, can someone please tell me what is the expression I
have to use to achieve this change.

Thank you

 

[John Vinson]

I am trying to update some data, I have a field NSN which has data as follow
1111-22-333-4444 and I want to change this data to read 223334444, so I want
to be able to remove the first 4 caracters and the dashes. I,m using
Microsoft Access 2000, can someone please tell me what is the expression I
have to use to achieve this change.

Thank you

The simplest way would be to update NSN to

Replace([NSN], "-", "")

This may not work if you don't have all the service packs for A2000
installed - early versions didn't recognize Replace() in queries. A
getaround is to write a dumb little wrapper function:

Public Function QReplace(strIn As String, strOld As String, _
strNew As String) As String
QReplace = Replace(strIn, strOld, strNew)
End Function

and use QReplace in the query instead of Replace.

John W. Vinson[MVP]

 

[Chris2]

I am trying to update some data, I have a field NSN which has data as follow
1111-22-333-4444 and I want to change this data to read 223334444, so I want
to be able to remove the first 4 caracters and the dashes. I,m using
Microsoft Access 2000, can someone please tell me what is the expression I
have to use to achieve this change.

Thank you

Replace(Right("1111-22-333-4444", len("1111-22-333-4444") - 4), "-",
"")