I do not have much knowledge of Crystal and we do not use states but why
don't you put every state in an array?
Search in the address for a string equal to one of the states in the array
and put a comma in front of it. I do not know the exact commands in Crystal
but I am sure this works and should not take more than 5 lines of code.
Ronald van der Linden
Macroscoop BV
-----Original Message-----
From: Thad Jacobs [mailto:tjacobs@...]
Sent: Thursday, March 21, 2002 5:30 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Re: Comma between City and State
This formula looks from the right to the left, but unfortunately assumes
that the correct state abbreviation is used. The amount of spaces in the
city name will have no bearing on the replacement of the space with the
comma. However, a two word state name will present problems.
Thaddeus
don't you put every state in an array?
Search in the address for a string equal to one of the states in the array
and put a comma in front of it. I do not know the exact commands in Crystal
but I am sure this works and should not take more than 5 lines of code.
Ronald van der Linden
Macroscoop BV
-----Original Message-----
From: Thad Jacobs [mailto:tjacobs@...]
Sent: Thursday, March 21, 2002 5:30 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Re: Comma between City and State
This formula looks from the right to the left, but unfortunately assumes
that the correct state abbreviation is used. The amount of spaces in the
city name will have no bearing on the replacement of the space with the
comma. However, a two word state name will present problems.
Thaddeus
--- In vantage@y..., Thad Jacobs <tjacobs@k...> wrote:
> This is the Revised, corrected version. The numbers were a little
mixed up
> in the last one....
>
> *Replace QUOTEADDR3 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR3}), "
> ", InStrRev (trim({quotehed.QUOTEADDR3}), " ") -1);
>
>
> IF instr({quotehed.QUOTEADDR4}, "USA") > 0 then
> mid({quotehed.QUOTEADDR3},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR3}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR3}
>
> *Replace QUOTEADDR4 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR4}), "
> ", InStrRev (trim({quotehed.QUOTEADDR4}), " ") -1);
>
> IF instr({quotehed.QUOTEADDR5}, "USA") > 0 then
> mid({quotehed.QUOTEADDR4},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR4}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR4}
>
>
>
> *Replace QUOTEADDR5 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR5}), "
> ", InStrRev (trim({quotehed.QUOTEADDR5}), " ") -1);
>
> IF instr({quotehed.QUOTEADDR6}, "USA") > 0 then
> mid({quotehed.QUOTEADDR5},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR5}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR5}
>
> -----Original Message-----
> From: Thad Jacobs [mailto:tjacobs@k...]
> Sent: Tuesday, March 19, 2002 3:44 PM
> To: 'vantage@y...'
> Subject: [Vantage] re: Comma between City and State
>
>
> The problem is that the "city, state zip" field will float between
> quotehed.QUOTEADDR3, quotehed.QUOTEADDR4, quotehed.QUOTEADDR5
> depending on the population of address2 and address3.
> So the solution is to verify that the next address field
contains "USA", and
> replace the space after the city with a ", ".
>
> To complicate things further, a city name may have more than one
space, you
> must look at the second space from the right.... So here's the
formulas. I
> think it may be the ugliest one I've written yet...
>
> *Replace QUOTEADDR3 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR3}), "
> ", InStrRev (trim({quotehed.QUOTEADDR3}), " ") -1);
>
>
> IF instr({quotehed.QUOTEADDR4}, "USA") > 0 then
> mid({quotehed.QUOTEADDR3},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR3}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR3}
>
> *Replace QUOTEADDR4 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR3}), "
> ", InStrRev (trim({quotehed.QUOTEADDR3}), " ") -1);
>
> IF instr({quotehed.QUOTEADDR5}, "USA") > 0 then
> mid({quotehed.QUOTEADDR4},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR4}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR4}
>
>
>
> *Replace QUOTEADDR6 with a formula containing the text:
>
> numberVar SecondSpaceFromRight := InStrRev (trim
({quotehed.QUOTEADDR3}), "
> ", InStrRev (trim({quotehed.QUOTEADDR3}), " ") -1);
>
> IF instr({quotehed.QUOTEADDR6}, "USA") > 0 then
> mid({quotehed.QUOTEADDR5},1 , SecondSpaceFromRight-1) + Replace
> ({quotehed.QUOTEADDR5}," " ,", " ,SecondSpaceFromRight, 1)
> else
> {quotehed.QUOTEADDR5}
>
>
> Simple enough, eh?
>
> HTH,
>
> Thaddeus
>
> -----Original Message-----
> From: dans@t... [mailto:dans@t...]
> Sent: Tuesday, March 19, 2002 11:21 AM
> To: Vantage Group (E-mail)
> Subject: [Vantage] Comma between City and State
>
>
> Novice Crystal Writer here.
> Â
> Can anyone tell me how to insert a comma between the city and state
in our
> crystal quote form? It is one field not 2.
> Â
> I may be worse than a novice but boss is bugging me.
> Â
> Thanks,
>
> Daniel C. Spoerl
> Estimator/MIS
> Toledo Metal Spinning Co.
> Phone: (419) 535-5931 ext. 203
> Fax:Â Â Â (419) 535-0565Â
> Personal E-fax: (707) 897-1708Â
> Email:Â Â Â HYPERLINK
> "mailto:dans@t..."dans@t...
> WebSite: HYPERLINK
> "http://www.toledometalspinning.com/"http://www.toledometalspinning.
com
>
HYPERLINK "http://www.toledometalspinning.com/products_configurator.ht
m" \n
>
>
>
> Â
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.336 / Virus Database: 188 - Release Date: 3/11/2002
>
>
>
> [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/
>
>
>
> 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/
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/
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/