UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> trim leading zeros from a text field in select statement    
 
   
estel_mm
post Sep 10 2010, 11:45 AM
Post #1

UtterAccess Member
Posts: 47



Hello everyone,
I have a text field of varying length which has leading zeros.
How can I remove these zeros in my select statement?
THere is an example of my data:
CODE
000000000000996918 1
000000000007104466 1
Go to the top of the page
 
+
theDBguy
post Sep 10 2010, 12:00 PM
Post #2

Access Wiki and Forums Moderator
Posts: 58,248
From: SoCal, USA



Hi,
And do you want to keep the space and the trailing number?
Go to the top of the page
 
+
estel_mm
post Sep 10 2010, 12:47 PM
Post #3

UtterAccess Member
Posts: 47



Trailing number yes. Space, doesn't really matter.
*EDIT***
It's actually probably best that I keep the end of the string the same way. I would just need to remove the leading zeros at the front. I tried using TRIM or SUBSTRING or CAST but to no avail...
Go to the top of the page
 
+
theDBguy
post Sep 10 2010, 05:14 PM
Post #4

Access Wiki and Forums Moderator
Posts: 58,248
From: SoCal, USA



Hi,
Thanks for the additional info. I'm afraid I am not aware of any function that will do that. You may have to roll out your own custom function.
Oimagine you would do something like this (in pseudocode):
1. Loop through the field value one character at a time.
2. Check if the value is equal to zero.
3. Increment a counter variable to indicate how many zeroes you find.
4. After all the leading zeroes are found, the counter should contain the position number of the last zero.
5. You can now use the Mid() function to return the truncated value of the field without the leading zeroes.
Hope that helps...
Go to the top of the page
 
+
theDBguy
post Sep 11 2010, 12:36 PM
Post #5

Access Wiki and Forums Moderator
Posts: 58,248
From: SoCal, USA



Hi,
Thanks to a hint from another UA member (Walter Niesz), I was able to put together the function below:
CODE
Public Function TrimZero(str As String) As String
If Left(str, 1) = "0" Then
    TrimZero = TrimZero(Mid(str, 2))
Else
    TrimZero = str
End If
    
End Function

Hope that helps...
Go to the top of the page
 
+
estel_mm
post Sep 13 2010, 08:53 AM
Post #6

UtterAccess Member
Posts: 47



Thanks DBguy.
I am trying to do this from an actual query though. Is there any way to do it from a Select statement? Could I call the function you provided in my select statement?
Go to the top of the page
 
+
estel_mm
post Sep 13 2010, 11:12 AM
Post #7

UtterAccess Member
Posts: 47



I actually found another thread similar to mine with a solution.
The CLng(fieldName) seems to work fine in trimming leading zeros.
Go to the top of the page
 
+
John Spencer
post Sep 13 2010, 11:39 AM
Post #8

UtterAccess VIP
Posts: 2,550
From: Columbia, Maryland



CLng will work to trim leading zeroes as long as the number value does not exceed the maximum permissible value for a Long integer and there is NO non-numeric characters such as a space.
Lng("000000000000996918 1") will error
CLng("000000000000996918") will return 996918 (note the dropped space + 1)
CLng("800000000000996918") will error
Val("000000000000996918 1") will return 996918 (note the dropped space + 1)
Clng will fail with any number greater than 2147483647
000000002147483647 is the maximum value you could "convert" with CLng
Go to the top of the page
 
+
estel_mm
post Sep 13 2010, 12:09 PM
Post #9

UtterAccess Member
Posts: 47



John, thanks for your help once again.
may have spaces in the string so I will have to use Val(). I don't think the number will exceed 2147483647.
Go to the top of the page
 
+
theDBguy
post Sep 13 2010, 11:50 PM
Post #10

Access Wiki and Forums Moderator
Posts: 58,248
From: SoCal, USA



Hi,
Sorry for the delay. Yes, you can call the above function in a query.
Odidn't tell you about the Val() function because I thought you said you wanted to preserve the space and trailing characters/digits.
Good luck!
Go to the top of the page
 
+
estel_mm
post Sep 20 2010, 02:26 PM
Post #11

UtterAccess Member
Posts: 47



Not a problem. Works perfectly.
Go to the top of the page
 
+
theDBguy
post Sep 20 2010, 02:33 PM
Post #12

Access Wiki and Forums Moderator
Posts: 58,248
From: SoCal, USA



Hi,
Glad to hear that. I thought others might be able to use a function like that too, so I posted the code in the Function Library.
Thanks to your question (and through encouragements from the other experts here), I was able to write that function and share it with others.
Good luck with your project.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 1st October 2014 - 03:17 PM

Tag cloud: