|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with trim!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 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. Show quoteHide quote "Damon" <nonse***@nononsense.com> wrote in message news:ZggPf.23224$57.11445@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 > 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". Show quoteHide quote "Dave" <nob***@nowhere.com> wrote in message news:uZxacHfQGHA.1416@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" <nonse***@nononsense.com> wrote in message > news:ZggPf.23224$57.11445@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 >> > > > 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 The problem you are having is because you are hard-coding the number ofbring > back 69-79 R. I wanted to be able to trim the r off. 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 Show quoteHide quote > 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" <nob***@nowhere.com> wrote in message > news:uZxacHfQGHA.1416@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" <nonse***@nononsense.com> wrote in message > > news:ZggPf.23224$57.11445@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 > >> > > > > > > "Damon" <nonse***@nononsense.com> wrote in message OK, the suggestion I gave is still validnews:XKgPf.63723$Dn4.32072@newsfe3-gui.ntli.net... > 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". Air Code dim s() as string dim i as integer dim t as string dim c as string for i = 1 to len(!m_add1) c = mid$(!m_add1,i,1) if isnumeric(c) then t =t & c else t = t & " " end if next s = split(trim$(t)," ") you now have the two numbers (as strings) in the array s This will work for any delimiter and if there is only a single number it will just return one element in the array. This is just one rough & ready method, given more information about the source data and its consistancy you should be able to come up with something much better. Dave O. "Damon" <nonse***@nononsense.com> wrote in message If the format of the address is something like the format shown, and if you news:XKgPf.63723$Dn4.32072@newsfe3-gui.ntli.net... > I can work out if they are "EVEN" or "ODD". assume that there are only even numbers (or only odd numbers) in a given address range then you could do something like: Range = (Val(Mid$(s1, InStr(s1, "-") + 1)) - Val(s1)) / 2 + 1 The above should return how many individual houses are included in the range. If the format is likely to be different in some respects then you will of course need to write a bit more code. Mike "Damon" <nonse***@nononsense.com> wrote in message .. . . unless of course you have an address range like:news:XKgPf.63723$Dn4.32072@newsfe3-gui.ntli.net... 129 - 179 4th Avenue" ;-) Mike "Damon" <nonse***@nononsense.com> wrote in message In the US? Sure, it's possible. Of course, you'll have to subscribe to the news:ZggPf.23224$57.11445@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? 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. 
Other interesting topics
MS, Enough is Enough!
Can someone please check my code? No Intellisense for items About The Hard Disk Serial Number Searching a string Convert Variant String to Double Form.Printform Creating an ActiveX control Lock Word window? WHERE clause in ADO query of Excel data Launch VB App with Arguments via Hyperlink |
|||||||||||||||||||||||
