Striping Leading Zero's

[Grant]

Does some bright spark know how to strip the leading zero's from the
following:
0000-0000035.74
to make it -35.74

The field length is always the same, but the number can be different. Like
the following:
000000000328.00

Heres what I have tried:
The CSng function appears to create a #Error when it comes across a negative
number such as 0000-0000035.74.
The replace function does not appear to be a valid function in Access. Only
in Excel workbooks.

Thanks in advance for the replies.

Grant.

 

[Douglas J. Steele]

Replace works in Access 2000 and 2002: what version of Access are you using?

In the meantime, the following function should work:

Function StripZeros(InputValue As String) As Single
Dim intMinus As Integer
Dim strNewString As String

strNewString = InputValue
intMinus = InStr(strNewString, "-")
Do While intMinus > 0
strNewString = Mid$(strNewString, intMinus)
intMinus = InStr(intMinus + 1, strNewString, "-")
Loop
StripZeros = Val(strNewString)

End Function

 

[Grant]

I am using the function in an update query. Maybe it is only available in a
module?

 

[Douglas J. Steele]

The code I gave you needs to be put into a module (and the module must be
named something other than the name of the function itself). Then you can
use it in an Update query.

 

[Grant]

Sorry wasn't clear about what I mean't.
I am using the Replace function in a query. It doesn't seem to be recognised
by Access 2000. Maybe the replace function is only available in a module.

Cheers.

 

[Douglas J. Steele]

Some versions of Access 2000 do have a problem that was apparently fixed by
a service pack (sorry, I don't remember the details).

If you can't get it to work in a query, write your own function to use the
built-in Replace function.

Something like:

Public Function MyReplace(sIn As String, _
sFind As String, _
sReplace As String, _
Optional nStart As Long = 1, _
Optional nCount As Long = -1, _
Optional bCompare As Long = vbBinaryCompare _
) As String

MyReplace = Replace(sIn, sFind, sReplace, _
nStart, nCount, bCompare)

End Function