Property Expression Syntax

Formatting Elements

Option Description

Title

This property changes a string to title case, i.e. makes the first letter of each word uppercase. Eg: Title(Street$)

Upper

Converts a string to uppercase characters. Eg: Upper(postcode$)

Lower

Converts a string to lowercase characters. Eg: Lower(postcode$)

Local

Gets the local representation of a number as a string, i.e. adds a separator for each 1000. Eg: Local(Value#)
Or to add a £ before the value “£”+Local(Value#)

Sentence

Changes a string to sentence case. Eg: Sentence(Details$)

Parse Date

Formats any Microsoft date format into one format. Eg: ParseDate(Date$)

In the example below, the first table shows the original format of the attributes.

The second table shows the effects of adding these formatting properties:

Title prefixed to Street$ / Town$ Expression field becomes Title(Street$) / Title(Town$)
Upper prefixed to postcode$ Expression field becomes Upper(postcode$)
“£”+Local prefixed to Value# Expression field becomes “£”+Local(Value#)
Sentence prefixed to Details$ Expression field becomes Sentence(Details$)
ParseDate prefixed to Date$ Expression field becomes ParseDate(Date$)

Useful to know

SIS Desktop 9.1 allows you to cascade expressions where multiple operators are used in a single expression. A string expression for example can be used in combination with a comparison operator like Iif.

iif(Left(test$,3)="abc","String begins with ABC","String does not begin with ABC")

If the test$ property is “ABCD” the true statement will be returned from the expression.

If the test$ string property is “BCDA” the false statement will be returned from the expression.

The expressions can use mathematical operators, functions, and spatial searches.

Note: A number of functions are available with or without a W Suffix. In practical use on words and numbers in languages such as English there will be no difference between text functions with or without the W Suffix, other than performance, where the W version is faster. See explanation below.

You can see the difference if using double byte characters such as Kanji (Japan). In these instances it is appropriate to use the expression without the W suffix.

  • Text functions with the W suffix use UTF-16 encoded strings (http://en.wikipedia.org/wiki/Utf-16) and all offsets/indices as parameters/return values are interpreted as word-indices of UTF-16 code words. (1 word = 2 bytes.)
  • Text functions without the W suffix use ANSI encoded strings (so they require string conversions UTF-16 to ANSI and perhaps ANSI to UTF-16 too and are therefore slower than their W counterparts) and any indices that are byte-indices into the ANSI multi-byte representation of the strings.

Numbers-as-strings that are cast to integer or absolute values (like decimal, float) are not always treated as numeric. To ensure they are, especially when sorting, use these casting methods: Integers as strings - use Int(Val("123")) Floats as strings - use Abs(Val("3.1415"))