Character pupose 3

[Jeffcis]

Hi,

I just need some clarification on the following specific characters and their purpose and how to use them in the FORMULA:
( ) parenthesis
{ }
[ ] square brackets
* asterisk
& ampersand
" " double quote
' ' single quote

Appreciate if you can provide a brief formula example on each.

Jeffcis

 

[IanWaterman]

() are used in functions

sum ({FieldName})

{} are used by Crystal to enclose fields you should not need to use them

[] enclose arrays

{NumericFieldName} in [1, 2, 3, 4]

* is a string wild card
{stringFieldName} like "stringpart*"

& or + can be used to concatenate fields
{stringFieldName1}&"/"&{stringFieldName2}

"" and '' are interchangeable in defining strings.
However, if a string contains ' eg it's then you must use ""

Ian

 

[CR85user]

Hi Jeffcis - Some examples below:

( ) parenthesis

Used to delimit grouped formulae. For example:

2 + 1 * 3

Results in the answer 5

Whereas

(2 + 1) * 3

Results in the answer 9

It considers the entire formula encapsulated by the parenthesis first before then processing. A more common use is within if-then-else statements for example.

{ }

Curly brackets are used to indicate fields or formulas specifically.

if {@field} = 1 and {@data} > 75 then {@Stuff} else {@fluff}

[ ] square brackets

Used to indicate arrayed data. Often used with the IN operator. If for example you wanted to select rows where a particular field was one of a number of values you could use:

{table.number} in [1,3,5,7,9,15,18]

This would then only return the records where a match is seen. This is effectively the same as saying:

{table.number} = 1 or
{table.number} = 3 or
{table.number} = 5 ...etc

* asterisk

Used as a wildcard. In strings or number matches it will match for any number of characters.

If for example you had raw data as such:

//{@Data}
ABC
ABCDEF
DEF
CDE
FGHI
ABCFGH
XYZF

Then the following queries would show the results below:

{@Data} like 'ABC*'

ABC
ABCDEF
ABCFGH

{@Data} like '*CDE*'

ABCDEF
CDE

{@Data} like '*F'

ABCDEF
DEF
XYZF

& ampersand

Literal meaning of and.

{@startdate} & ' to ' & {@enddate}

" " double quote
' ' single quote

No difference between the two that I have seen. Most people seem to tend to use " however my personal preference is '

It may simply be due to the difference of UK / US keyboard layouts or simply habit through tuition or similar afaik.


Please note - All of these operators are very well documented in the help guide built into crystal with some very useful examples. Also there are other guides available for specific versions. Perhaps try

http://www.crystalbook.com/crtcr/extras/10/AppA.pdf

for some basics.

'J

 

[CR85user]

Ah yes - forgot to mention the point Ian raised about the parenthesis for indicating the relevant field for an operator.

isnull({table.field}) or totext({table.field},0} like '123'

Note that the brackets () are used to contain the elements of the operators function.

For example mid({@field},3,5)

The 3,5 part indicating start point and length must be within the paranthesis.

'J

 

[Jeffcis]

Thanks CR85user for your valuable input. Now it does help to understand how these specific operators can be used in formula. I wish I can both for you and ian.

Thanks Ian for your feedback too.

 

[CR85user]

I think you can thank multiple persons - Just to be sure I will add a star for Ian also as he made many valid points.

'J

 

[Jeffcis]

What is it mean if my formula result says FALSE when I used the below formula

{@Data} like '*Stella*'

When I browse my {@Data} there are numerious product name Stella.

 

[CR85user]

A formula like:

{@Data} like '*Stella*'

Will return two possible values if there is no delimited output.

True or false.

If the field matches the query then it will generate true (Remember it is case sensitive - To get around that get used ot using UCASE. For example ucase({@Data})like '*STELLA*'

If you want the output to be more useful then you can use it in a number of ways.

IF {@Data} like '*Stella*' THEN 'Whoohoo - gods own ale!' ELSE 'Watery grog...'

Remember to ensuer that all potential outputs of an if-then-else grouping must be the same datatype.

You could also display different fields / values based on the test made in the formula.

if {@Data} like '*Stella*' then {table1.stock} else (table2.sales}

Have fun experimenting.

'J