Searching an entire field using the "If" function

[kelturner]

I would like to create a script using the "If" Function to search for commas within specified fields. For example if a field contains the value "5510" then I would like to add a comma to the end of the field. If there is already a comma present,(ex. "5510,"), then no comma would be added. My problem is that I don't know how to define the if statement to search the whole field for commas. When I use: If(MyField = ",")...etc. it will only register that there is a comma present if it is the only value within the field, if there are numbers preceding the comma, it doesn't work.

Can anyone help me with this? Thank you in advance!

[Chuck]

Use the PatternCount function, which returns the number of times a character or string of characters appears within a field.

PatternCount( "123-456-7890", "-" ) = 2

PatternCount( "Chuck Ross", "Chuck" ) = 1

PatternCount( "5510,", "," ) = 1

So instead of

If[ MyField = "," ]

You would use

If[ PatternCount( MyField, "," ) > 0 ]

or

If[ PatternCount( MyField, "," ) = 1 ]

Chuck

[stephiesmith]

Can't he also use the greater than or equal to in his calculation?

If[MyField >= "," ]

I do that with my urls in my web-enabled database, i.e., my website field has auto enter data of "http://" for all new records and users are supposed to add the web addresses if they know them. I don't want incomplete urls showing up so I have the url field calculated as:

If[website > "http://", website, ""

[Chuck]

Stephie,

Your calculation, If(website > "http://", website, ""), will only work if the URL begins with something after "h" in the alphabet, or after "ht", etc. So it would work if they entered "www.apple.com" but not if they entered "apple.com". The reason it probably works in most, if not all, cases for you is that just about every web site out there begins with "www" which in the sort order would come after "http://". A more accurate calculation, which would work under more circumstances, would be:

If( PatternCount( website, "http://" ) = 0, website, "" )

Chuck

[kelturner]

Thank you! The Pattern Count Function worked great! I also tried using >="," ,but that wouldn't search the entire field properly if there were additional numbers in the field.Thanks again. -Kel