How to trim 000s in VB 1

[intrepidtechie]

Hello Friends,
This is the situation. I have a Access table that gets new data imported from another table (from the mainframe) through a VB process. Once the table gets updated one of the columns have extra precedding 00s..for example..instead of showing 384799474..it shows 00000384799474. Also the other thing I want to mention is the number of precedding 00s are not consistent. some are field values have 3 zeros while some might have only 2 zeros.

Is there any method to remove to zeros.
I can go with 2 options:
1. either remove the zeros before it gets imported..that means right there in the VB import routine..
2. Have a query that runs automatically each time the table gets udpated.

Please help at the earliest..

Thanks..

 

[SkipVought]

Hi,

Chances are the mainframe program is extracting a numeric value and padding the field with zeros (since spaces in a numeric field can make some programs bomb).

I'd venture to say that your databse field is a TEXT field and NOT NUMERIC. With a TEXT field, such ZEROS are significant. With NUMERIC fields, they are merely FORMATTING. Why not change the field type to a NUMERIC type?



Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[MarkSweetland]

You can convert the value number using Format(colName, "#"). This formats the colName value to a numerical format without the leading zeros. However, if the value in colName contains a character, the function will leave the leading zeros intact.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[intrepidtechie]

Skip you are correct. the field is set as Text. And I tried changing it to a Numberic but it gave me some errors while changing the format and gave me a empty column..didt work..

And Mark..can u explain to me please how the "Format(colName, "#"). " thing is done..

 

[fredericofonseca]

First of all you need to make sure that the mainframe data being sent down is always numeric. If it isn't then you cannot change the field to numeric.

You didn't say how you were getting the data.
Is the VB program accessing the mainframe files directly or is the mainframe sending the file down (FTP or similar) and then VB is processing that file?

If your VB program is doing something like

read mainframe into tmpvariable
write tmpvariable into access table

then the best is to use the format thing.

Function FormatLeadingZeros(lngNum As Long) As String
' Formats number with leading zeros.

FormatLeadingZeros = Format$(lngNum, "00000")
End Function

will return a string of a number with 5 digits, e.g.
01234 or 00123

Similiary if we replace "00000" with "#" it will return the number without leading zeros.


Where to do this will really depend on how you are processing the data, which you have not mentioned. (sample code may be required)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[intrepidtechie]

Hello Frederico,
The data is sent from Mainframe to Access via a VB batch process. Its a simple file transfer code that extracts field by field from DB2 to Access.
Once the data is in Access it adds theses extra 000s (preceding) to one of the columns.
I am not sure if the Db2 table also has extra 000s..maybe I should find that out (good question).

If you could please explain to me this part..that would be great
==================================
then the best is to use the format thing.

Function FormatLeadingZeros(lngNum As Long) As String
' Formats number with leading zeros.

FormatLeadingZeros = Format$(lngNum, "00000")
End Function

will return a string of a number with 5 digits, e.g.
01234 or 00123

Similiary if we replace "00000" with "#" it will return the number without leading zeros.

==================================

 

[fredericofonseca]

On your VB editor type format, highlight it and press F1. Then look at the help text.

"format" is a standard VB function. My example is a User Defined Function that uses the format function to return you a string with a predefined format without you having to type that format everytime.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[557]

give val(0000000453453) or whichever number you want inside the bracket and you'll get 453453 as result