How do I remove leading zeros?

[Frank Martin]

I have downloads from the bank including
check numbers containing leading zeros.

eg 0002356, where what I want is just 2356.

How do I enter formatting in the query column
to get rid of these leading zeros?
Please help, Frank

 

[Vsn]

I do suppose your 'bank supplied number' is a text string.

You could do this straigh, like below:-

Using Val([fBankNumber])

SELECT tblBankNumbers.fID, tblBankNumbers.fBankNumber, Val([fBankNumber]) AS
Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;


Or using a function (witch could be more flexible and give more control if
needed):-

SELECT tblBankNumbers.fID, tblBankNumbers.fBankNumber,
ReturnNum([fBankNumber]) AS Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;

Function ReturnNum(stgX As String) As Long
ReturnNum = Val(stgX)
End Function

Success,
Ludovic

 

[Frank Martin]

Thanks, but if I use your first method I get
"#error" in any blank fields.

I find that if I put "nnnn" in the criteria
field, this works OK; at least for my
application.

I do suppose your 'bank supplied number' is
a text string.

You could do this straigh, like below:-

Using Val([fBankNumber])

SELECT tblBankNumbers.fID,
tblBankNumbers.fBankNumber,
Val([fBankNumber]) AS Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;


Or using a function (witch could be more
flexible and give more control if needed):-

SELECT tblBankNumbers.fID,
tblBankNumbers.fBankNumber,
ReturnNum([fBankNumber]) AS Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;

Function ReturnNum(stgX As String) As Long
ReturnNum = Val(stgX)
End Function

Success,
Ludovic

message

I have downloads from the bank including
check numbers containing leading zeros.

eg 0002356, where what I want is just
2356.

How do I enter formatting in the query
column to get rid of these leading zeros?
Please help, Frank

 

[Vsn]

Sorry, I did not allow for blank fields (blank cheque numbers?), you could
do so as follows:-

IIF(IsNull([fBankNumber]),0,Val([fBankNumber]))

Which wil result in a 0 for a blank field.

Anyhow, if you managed to get to your target, your done.

Ludovic

Thanks, but if I use your first method I get "#error" in any blank
fields.

I find that if I put "nnnn" in the criteria field, this works OK; at least
for my application.

I do suppose your 'bank supplied number' is a text string.

You could do this straigh, like below:-

Using Val([fBankNumber])

SELECT tblBankNumbers.fID, tblBankNumbers.fBankNumber, Val([fBankNumber])
AS Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;


Or using a function (witch could be more flexible and give more control
if needed):-

SELECT tblBankNumbers.fID, tblBankNumbers.fBankNumber,
ReturnNum([fBankNumber]) AS Clean
FROM tblBankNumbers
WITH OWNERACCESS OPTION;

Function ReturnNum(stgX As String) As Long
ReturnNum = Val(stgX)
End Function

Success,
Ludovic

I have downloads from the bank including check numbers containing leading
zeros.

eg 0002356, where what I want is just 2356.

How do I enter formatting in the query column to get rid of these
leading zeros?
Please help, Frank