Worked like a charm! Thanks Ed & Todd.
What a relief! I guess I should have been more specific!
Thanks again!
Juliet
-----Original Message-----
From: Giallombardo, Ed [mailto:egiallombardo@...]
Sent: Wednesday, May 22, 2002 1:08 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Q
This is what I use to pull the salesperson:
Entry(1,SalesRepList,Chr(126))
126 is the ASCII code for the tilde. I have done joins on this to
SalesRep.SalesRepCode. If you're trying to join it to a numeric field
you
would probably have to convert it first using NUMERIC(text).
- Ed
-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Wednesday, May 22, 2002 12:57 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
I said I would report back what I found, so here goes...
These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.
IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.
IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.
Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.
Thanks for all of your help.
Juliet
-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.
Juliet
-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
At 11:53 AM 5/16/2002 -0500, you wrote:
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)
-WC
Yahoo! Groups Sponsor
ADVERTISEMENT
<http://rd.yahoo.com/M=226020.2067224.3532861.1971030/D=egroupweb/S=1705
0071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http:
//ww
w.tv/en/landing.shtml&HHcode=yhc3my2>
<http://us.adserver.yahoo.com/l?M=226020.2067224.3532861.1971030/D=egrou
pmai
l/S=1705007183:HM/A=1073422/rand=268839557>
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/
What a relief! I guess I should have been more specific!
Thanks again!
Juliet
-----Original Message-----
From: Giallombardo, Ed [mailto:egiallombardo@...]
Sent: Wednesday, May 22, 2002 1:08 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Q
This is what I use to pull the salesperson:
Entry(1,SalesRepList,Chr(126))
126 is the ASCII code for the tilde. I have done joins on this to
SalesRep.SalesRepCode. If you're trying to join it to a numeric field
you
would probably have to convert it first using NUMERIC(text).
- Ed
-----Original Message-----
From: Juliet Martin [mailto:jmartin@...]
Sent: Wednesday, May 22, 2002 12:57 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
I said I would report back what I found, so here goes...
These were the two functions that I got to work. You can't do a join on
them (too complex I think) so it was all for nothing.
IIF(LENGTH(SalesRepList) = 6,SUBSTRING(SalesRepList,1,2),
SUBSTRING(SalesRepList,1,3))
For our situation the length will either be 6 or 7 digits long.
IIF(SUBSTRING(SalesRepList,3,1) <> '0', " ","0")
For our situation if their are three digits the last one will always be
0. If the 3rd digit is not a zero but a ~ then it must be two digits
long.
Any function I tried with the ~ it wouldn't work. If I put the quotes
around the ~, I would get an error to terminate the string.
Thanks for all of your help.
Juliet
-----Original Message-----
From: Juliet Martin
Sent: Friday, May 17, 2002 7:16 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
Well, Still working on this. I found that I don't need the quotes
around the ~ in all of the suggestions. Thanks a lot by the way! Maybe
I am not doing things right but I am not getting the correct results.
If I find an answer I will post it.
Juliet
-----Original Message-----
From: Wayne Cox [mailto:wmc20@...]
Sent: Thursday, May 16, 2002 12:07 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Q
At 11:53 AM 5/16/2002 -0500, you wrote:
>The field data would look like thisI'd go with either of these:
>20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
>200~~~~~~~~~~~~~~~~~~~~~~~~~~~~, etc
ENTRY(1, FieldData, '~')
SUBSTRING(FieldData, 1, (INDEX(FieldData, '~') - 1)
-WC
Yahoo! Groups Sponsor
ADVERTISEMENT
<http://rd.yahoo.com/M=226020.2067224.3532861.1971030/D=egroupweb/S=1705
0071
83:HM/A=1073422/R=0/*http://www.qksrv.net/click-1084497-610300?url=http:
//ww
w.tv/en/landing.shtml&HHcode=yhc3my2>
<http://us.adserver.yahoo.com/l?M=226020.2067224.3532861.1971030/D=egrou
pmai
l/S=1705007183:HM/A=1073422/rand=268839557>
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/