Calling VB function from Access to update a field

[weasel7711]

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.

[AndreRet]

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

[weasel7711]

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

[AndreRet]

Are you using vb6 and access or VBA within access?

[weasel7711]

VBA within access.

[AndreRet]

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...

[weasel7711]

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

[AndreRet]

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

[weasel7711]

Thanks for the help, I will try it out.

[AndreRet]

Cool, if you do encounter errors, let me know and I'll test the code.:)