June 10, 2015 at 2:19 pm
W4Designs (6/10/2015)
CRAP!!! We're are right there and I don't see the problem either. I've matched every paren and they all match up unless this mess has made me go blind.
Post the code in here.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 2:24 pm
I found the problem. It was where the " " was towards the end of address_2. It needed to be moved over a flew places from where it was.
It WAS like this:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))
And needed to be like this:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1,50))," ",1))
But, address_2 and 3 and now having the same data as address_1 if the address is longer 50 characters. Trying to figure that one out now.
June 10, 2015 at 2:34 pm
W4Designs (6/10/2015)
I found the problem. It was where the " " was towards the end of address_2. It needed to be moved over a flew places from where it was.It WAS like this:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))And needed to be like this:
SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1,50))," ",1))But, address_2 and 3 and now having the same data as address_1 if the address is longer 50 characters. Trying to figure that one out now.
First place to look, 2nd parameter of SUBSTRING. This determines the starting character.
For Address_2 it's LEN(Address_1) + 1
For Address_3 it's LEN(Address_1) + LEN(Address_2) + 1
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 2:47 pm
It appears to be ignoring the references to address_1 and address_2 in address_2 and address_3.
June 10, 2015 at 2:54 pm
I did the following workaround that solved the problem of it ignoring references to address_1 and address_2 in address_2 and address_3. It ain't purdy, but it works.
SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))
SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + LEN(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))) + 1,50))," ",1))
Thank you sooooo much for your help. I really appreciate it.
June 10, 2015 at 2:57 pm
Only things I can think of right now is make sure each derived column name is correctly spelled and always has the same case. Address_1 and address_1 may be considered as different.
Also, use [ and ] around all column names, like [address_1].
I would be surprised if this fixes anything, but it would hurt to be strict about the syntax.
Also, make sure the columns get derived in the right order. Again, I would expect an error if that was not correct.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 3:43 pm
I did all that to no avail.
I ended up doing the following which ain't purdy, but it works.
SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))
SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + LEN(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50))," ",1))) + 1,50))," ",1))
Thank you very much for all your help!
Viewing 7 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply