Update Query Access 2000

A quick and dirty answer is to select the Field (Column)and use Edit>Replace. You must specify any part of the field as opposed to the whole field and Replace 0 with 1

Thankyou for your response However I wish my task was that simple. Perhaps I should explain My Number Entry In the field is an Employee ID which is present at the top of a sheet where my imported data came from the o,s represent bundles of work thatthis employee has done I need top update the o,s to the Employee ID the problem is that I am importing a file consisting of 100 Employee sheets therfore my field looks like 10000200030000000000040000000005000000060000 etc Thanks

Hello bmcilwraith,

An update query, in my opinion, will not be able to achieve what you intend. Here is a VBA procedure that does exactly what you require :

'-----
Sub ReplZeros()

' Code By SanganakSakha 8/11/01
' No error checking or data validation
'
' Replaces zeros with previous digit
' eg 100200300 becomes 111222333
'
Dim rs1 As DAO.Recordset
Dim x, y, i, z, nx, w

Set rs1 = CurrentDb.OpenRecordset("")
rs1.MoveFirst

While rs1.EOF = False
x = Trim(rs1.Fields(""))
y = Left(x, 1)
' leftmost digit
z = Len(x)
nx = y
' New string being created
For i = 2 To z
w = Mid(x, i, 1)
If w = "0" Then
nx = nx & y
Else
y = w
nx = nx & w
End If
Next
rs1.Edit
rs1.Fields("TheFieldName") =nx
rs1.Update
rs1.MoveNext
Wend
End Sub
' -----

Paste this code in a module in the database and run it. You will have to substitute appropriate table and field name before doing so.
(You also need to make a reference to DAO in VBA IDE)

Iwould be glad to provide any further assistance you may need in this regard.

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This question was closed by the author