I am using MS Access currently because it allows quick prototyping of database applications. You can easily link tables to SQL databases and generate reports and queries. One issue I had lately was a text field containing a number with leading zeros. After writing a loop to remove the zeros I discovered this simple way of achieving the desired result.
Replace( LTRIM(Replace( fldName, "0", " ")), " ", "0")
Here is a description of whats happening starting with the inner part of the statment and assuming fldName contains “0001205” :-
|the inner replace() replaces all zeros with space,
there are three spaces before 12 5
|” 12 5″|
|ltrim() removes all spaces from left side,
notice the space is left between 2 and 5
|the outer replace() replaces all spaces with zeros||“1205”|
This should work for all visual basic implementations such as ms access, excel, word, etc.