Help with trim!
in [Visual Basic]
|
Prev: Network Drive Not Ready
Next: Soap 3.0 Error Help
From: Damon on 7 Mar 2006 08:55 Hi, I have an address that comes though which may be 69-79 RUBY STREET or 129-179 RUBY STREET etc. I want to be able to do a sum on these addresses i.e. 79-69 or 179-129 so I can tell how many houses are within that specific block. Is this possible? Appreciate the help Thanks Damon
From: Dave on 7 Mar 2006 09:17 What has this got to do with Trim? that strips leading and trailing spaces from a string. Listen carefully Subtract the small number from the big number. Presumably you want to split the string into numbers first, it would help if you worked out what you wanted to do and worded yor question clearly. There are any number of ways to split the string, the easiest would be to iterate through the string, copy numbers to a new string, then once the first number ends move on to a new variable. Another possiblity is to swap every non-numeric with a space, then use Trim and then Split with a space as a delimiter. Of course it will never work because you have no way of knowing if the address just refers to odd or even numbers or all of them, there is also the possibilty of sub-addresses like 69a Ruby St, 69b Ruby St. etc. Dave O. "Damon" <nonsense(a)nononsense.com> wrote in message news:ZggPf.23224$57.11445(a)newsfe3-win.ntli.net... > Hi, > > I have an address that comes though which may be 69-79 RUBY STREET or > 129-179 RUBY STREET etc. I want to be able to do a sum on these addresses > i.e. 79-69 or 179-129 so I can tell how many houses are within that > specific block. Is this possible? > > Appreciate the help > > Thanks > > Damon >
From: Damon on 7 Mar 2006 09:27 Hi, I have placed this under trim because if I used "Range = RTrim(Left(!m_add1, 7))" then that would bring back 129-179 but if it was 69-79 that would bring back 69-79 R. I wanted to be able to trim the r off. Also the reason I am doing the sum in the first place is to match it up with the recordcount to see if they are CONTINUOUS, if the sum is less than the recordcount I can work out if they are "EVEN" or "ODD". "Dave" <nobody(a)nowhere.com> wrote in message news:uZxacHfQGHA.1416(a)TK2MSFTNGP12.phx.gbl... > What has this got to do with Trim? that strips leading and trailing spaces > from a string. > > Listen carefully > > Subtract the small number from the big number. > > Presumably you want to split the string into numbers first, it would help > if you worked out what you wanted to do and worded yor question clearly. > > There are any number of ways to split the string, the easiest would be to > iterate through the string, copy numbers to a new string, then once the > first number ends move on to a new variable. Another possiblity is to swap > every non-numeric with a space, then use Trim and then Split with a space > as a delimiter. > > Of course it will never work because you have no way of knowing if the > address just refers to odd or even numbers or all of them, there is also > the possibilty of sub-addresses like 69a Ruby St, 69b Ruby St. etc. > > Dave O. > > > "Damon" <nonsense(a)nononsense.com> wrote in message > news:ZggPf.23224$57.11445(a)newsfe3-win.ntli.net... >> Hi, >> >> I have an address that comes though which may be 69-79 RUBY STREET or >> 129-179 RUBY STREET etc. I want to be able to do a sum on these >> addresses i.e. 79-69 or 179-129 so I can tell how many houses are within >> that specific block. Is this possible? >> >> Appreciate the help >> >> Thanks >> >> Damon >> > >
From: Jeff Johnson [MVP: VB] on 7 Mar 2006 09:29 "Damon" <nonsense(a)nononsense.com> wrote in message news:ZggPf.23224$57.11445(a)newsfe3-win.ntli.net... > I have an address that comes though which may be 69-79 RUBY STREET or > 129-179 RUBY STREET etc. I want to be able to do a sum on these addresses > i.e. 79-69 or 179-129 so I can tell how many houses are within that > specific block. Is this possible? In the US? Sure, it's possible. Of course, you'll have to subscribe to the Post Office's database so that you can do Delivery Point Verification (DPV). You see, just because the house numbers fall within a given RANGE doesn't mean that they ALL exist. Translation: you're not going to be able to do for real this without purchasing a third-party tool.
From: Rick Rothstein [MVP - Visual Basic] on 7 Mar 2006 10:34
> I have placed this under trim because if I used "Range = RTrim(Left(!m_add1, > 7))" then that would bring back 129-179 but if it was 69-79 that would bring > back 69-79 R. I wanted to be able to trim the r off. The problem you are having is because you are hard-coding the number of characters for the Left function to return and, as you see, that number is not always correct. What you need to do is let the program figure out where the first blank character is instead of trying guess at its location. VB has a function named InStr with will do that. It returns the first occurrence of a specified substring within a given text string after an optionally (defaulted to 1) specified starting location within the string. So, you could do this... FirstBlankPosition = InStr(!m_add1, " ") Range = Left(!m_add1, FirstBlankPosition - 1) Notice that I have eliminated the need for the RTrim function call by doing this. Since InStr is finding the first blank, there are none before it, specifying one less than that position (to the Left function) automatically excludes the blank. The only thing you have to worry about is if there are ever any blanks placed around that dash separating the numbers. Rick Also the reason I am > doing the sum in the first place is to match it up with the recordcount to > see if they are CONTINUOUS, if the sum is less than the recordcount I can > work out if they are "EVEN" or "ODD". > > "Dave" <nobody(a)nowhere.com> wrote in message > news:uZxacHfQGHA.1416(a)TK2MSFTNGP12.phx.gbl... > > What has this got to do with Trim? that strips leading and trailing spaces > > from a string. > > > > Listen carefully > > > > Subtract the small number from the big number. > > > > Presumably you want to split the string into numbers first, it would help > > if you worked out what you wanted to do and worded yor question clearly. > > > > There are any number of ways to split the string, the easiest would be to > > iterate through the string, copy numbers to a new string, then once the > > first number ends move on to a new variable. Another possiblity is to swap > > every non-numeric with a space, then use Trim and then Split with a space > > as a delimiter. > > > > Of course it will never work because you have no way of knowing if the > > address just refers to odd or even numbers or all of them, there is also > > the possibilty of sub-addresses like 69a Ruby St, 69b Ruby St. etc. > > > > Dave O. > > > > > > "Damon" <nonsense(a)nononsense.com> wrote in message > > news:ZggPf.23224$57.11445(a)newsfe3-win.ntli.net... > >> Hi, > >> > >> I have an address that comes though which may be 69-79 RUBY STREET or > >> 129-179 RUBY STREET etc. I want to be able to do a sum on these > >> addresses i.e. 79-69 or 179-129 so I can tell how many houses are within > >> that specific block. Is this possible? > >> > >> Appreciate the help > >> > >> Thanks > >> > >> Damon > >> > > > > > > |