How to delete first value in records?

[sueviolet]

Hi,


I asked a similar problem a while ago but I can't get the posted answer to fix my new problem.

Its pretty simply really, I am just having problems with syntax.

I have a field with all records with a letter as the first value:

N591120
N584850


I want the this first value erased:

591120
584850

How do I do this?

Thanks so much for your help

 

[FilipT]

length

I think I know a solution, but I will have to try it first.
But there is one importent question, is the lengt of the texst always the same.
Like in your example, you have first a lettre, and then 6 numbers.
If every record has one lettre and 6 numbers, then I can fix it.
And would it not be better to save the new value in an other field, so that you don't overwrite the original one?
I find it always better to keep the original, you never know where it is good for. Let me know the length's, and I'll make tomorrow an example.

 

[FilipT]

change record

Had to think about it for a moment, but think that I got what you want.
I save the new number in a new colum, because the module that changes the value takes away the first caracter. So if you would save it in the same colom and run it again, you would take again a karakter away.Function First_gone()
Dim Db As Database
Dim Rs As Recordset
Dim Tabel As String
Dim RcCount As Long 'Number of records
Dim Value As String 'The value minus 1 value.

Tabel = "SELECT Table1.* FROM Table1;"

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(Tabel)
With Rs
.MoveFirst
Value = Mid(!number1, 2)
.Edit
!number_new = Value
.Update
RcCount = .RecordCount
Do Until (.AbsolutePosition + 1) = RcCount
.MoveNext
Value = Mid(!number1, 2)
.Edit
!number_new = Value
.Update
Loop
End With
End Function

 

[sueviolet]

code doesn't work

Hi this code has errors and doesn't work. I am not experienced enough in VB to figure this out - could someone please help me.



Dim Db As Database
Dim Rs As Recordset
Dim Tabel As String
Dim RcCount As Long 'Number of records
Dim Value As String 'The value minus 1 value.

Tabel = "SELECT HYDRAT_STATIONS_ALL.EASTING FROM HYDRAT_STATIONS_ALL;"

Set Db = CurrentDb
Set Rs = Db.OpenRecordset(Tabel)
With Rs
.MoveFirst
Value = Mid(!number1, 2)
.Edit
!number_new = Value
.Update
RcCount = .RecordCount
Do Until (.AbsolutePosition + 1) = RcCount
.MoveNext
Value = Mid(!number1, 2)
.Edit
!number_new = Value
.Update
Loop
End With
End Function

 

[Nouba]

you could also use an update query like

UPDATE HYDRAT_STATIONS_ALL AS H
  SET H.EASTINGA = Right$([EASTING], Len([EASTING]) - 1)

HTH nouba

 

[sueviolet]

Thankyou thankyou - the following code worked:



UPDATE HYDRAT_STATIONS_ALL AS H
SET H.EASTING = Right$([EASTING], Len([EASTING]) - 1