Query Formatting Data on export

[Guest]

I am exporting data from the database and using a query to gather that data.
There is a field that is causing problems. This field is kept in a different
database that we access via ODBC, and it is a text field.

I need to convert it to number field or remove the leading 0's from the
front of the data. The catch is I can't use the VAL function because the
data has decimals. And these decimals can end in zeros. Here are some
examples.

000000453.10 needs to be exported as 453.10
000000332.1000 needs to be exported as 332.1000
000001345.230 needs to be exported as 1345.230

Anyone have a formula or know a way to deal with this?

 

[Ken Snell \(MVP\)]

Create a public function (in a regular module) that will reformat the text
string by stripping the leading zeroes:

Public Function StripLeadingZeroesFromTextNumber(varOriginalString) As
String
Dim lngCount As Long
Dim strTemp As String, strNew As String
strTemp = Nz(varOriginalString, "")'
strNew = strTemp
For lngCount = 1 To Len(strTemp)
If Left(strNew, 1) Like "0" Then strNew = Mid(strNew, 2)
Next lngCount
StripLeadingZeroesFromTextNumber = strNew
Exit Function
End Function


Then use this function in an expression in a calculated field in your
exported query:

SELECT Field1, Field2, Field3,
StripLeadingZeroesFromTextNumber(Field4), Field5
FROM YourTableName;