The following table describes each widget and its associated data type:
| Widget | Data Type |
|---|---|
| Text Box | Text |
| Drop-down lists | Text |
| Data Link Dropdown Lists | Text |
| Radio Button | Text |
| Segment | Text |
| Checkbox | Logical (True/False) |
| Number | Number |
| Calendar | Number |
| Time | Number |
| Timestamp | Number |
Most form items contain data of type text. When used in an expression, the value must be contained within double quotes. Consider the following example in which we have a drop-down list called Gender with the entries Male and Female.
If we were to place an additional text box beneath this and wanted to use an expression to populate the value based upon the selection of gender so that:
If selected gender is Male, populate the text box with Hello sir
If selected gender is Female, populate the text box with Hello madam
We would write the following expression:
="Hello" & IF(Gender = "Male", "sir", "madam")
Note that each text string is completely enclosed by double quotes. Also note that we've used the ampersand operator (&) to 'glue' or concatenate two text strings together. In this case we're concatenate the string Hello with either sir or madam.
Two pieces of text can be combined into one, or can be compared.
The value of a calendar widget contains a number which represents the number of days elapsed calculated from an epoch of 31st December 1899. Therefore, if you were to select a date of 30th May 2021, the value would contain the number 41,059.
The value of a time widget contains a value representing the fraction of a 24 hour day with 0 being midnight. A value of 0.5 would therefore represent midday,
A Time’s value is a fraction of a day. 0 is midnight, midday is 0.5.
One number can, therefore, represent a date and a time together. 41,059.5 is midday on the 30th May 2012.
Checkboxes have “logical” values – these values are either true or false. A checkbox is “true” if it is
ticked, “false” if it is not.
Logical values are also the results of comparing other values. More on this later.
In most cases, you will want to use the values from other items on the form. You do this by using the
caption of the item from which you want the value.
=Age < 18
Note that this is not text data, it a name of a form item, so it doesn’t have double-quotes around it.
However, if the caption has spaces in it, then you must put single-quotes around it. Same if it has a “/”, “+”, “-“ or any other special symbol.
=’Age at last birthday’ < 18
The name needs to match exactly. If the caption is “Age”, then you must write “Age”, not “age”. The form designer will complain if it cannot find an item with the caption you’ve given.
For Best Results
Only take values from items before (above) the one for which you are writing the formula.
Avoid having double or single quotes in captions.
Don’t have two items on a form with the same caption if you want to use them in a formula.
Watch out for double spaces in captions, or a space at the end.