Hello, I am new to Access and VB, but I have used C# and C++.
I am trying to update a whole bunch of records in a table, only one field. For example

[Employees]
[ EmpCode ] [ FirstN  ] [ LastN      ]
[ 019871  ] [ John    ] [ Smith      ]
[  19787  ] [ Bob     ] [ Jones      ]
[ 08097Y  ] [ Kenneth ] [ Noisewater ]
[  IJK87  ] [ ...     ] [ ...        ]
[  97766  ]      
[ 0CR487  ]

I want to remove all the leading zeros in any of those entries in the EmpCode column.

So I brought up the module editor and wrote this Function:

'this function takes a string as an argument and returns a string
'if there is a zero at the beginning of the string it will be removed
Public Function removeZero(strField As String) As String
    'If the first character is zero, proceed
    If (strField.Substr(0, 1) = "0") Then
    'remove the first character which should be a zero
    'return the new string
    removeZero = Right(strField, (Len(strField) - 1))
    End If
End Function

How would I call it? Query? Form? I just want to update the table. I figure I can call the function somehow and pass it the EmpCode as a string. Thanks for the help.

Dim strTest As String
'First check to see if the string starts with a 0
strTest = Mid(strField, 1, 1)

If strTest = "0" Then
'It is a zero, remove the zero...
strField = Right(strField, Len(strField) - 1)
End If

Edited 6 Years Ago by AndreRet: n/a

Thanks for the correction, but how do I call the function from MS Access? Can VB code be embedded in a query?

The code is a bit different in access. You'll be using something like -

UPDATE MyTable SET MyField = Right(MyField, Len(MyField) - 1);

Or

UPDATE MyTable SET MyField = Replace(MyField, "'", "")

Sorry about that, thought you were using vb6...

Comments
Helpful

So you can just write the VB code in the query? What about conditional statements, does it follow the same logic?

Something like:

If (MyTable.MyField.Substr(0, 1) = "0") Then
UPDATE MyTable SET MyField = Right(MyField, Len(MyField) - 1);
End If

Your coding seems 100%, although I have not tested it. You should not have a problem with conditional statements in vba.

This article has been dead for over six months. Start a new discussion instead.