Share this Page URL

Chapter 3. Managing the Consistency, For... > Expressions and the Expression Build... - Pg. 97

Managing the Consistency, Format, and Integrity of Your Data Set the Format property 1. 2. 3. 4. Open the MarketingCampaigns table in Design view. Click in the EndDate field, and then click in the Format property box. Select Medium Date from the list of date formats. Save the changes to the table, and then close the table. 97 International formats Many databases need to display information to employees and customers in many different countries. Access can display data--especially currency and date and time data--in a range of international formats. Access uses the regional settings in Control Panel to determine the number and date/time settings for a computer. If you see data that's not displayed in the format you need, check the regional settings in Control Panel. Also, changes you make to regional settings are reflected in currency fields. These changes affect only the format, however; currency values are not converted. Keep this in mind if you move data from a computer with one regional setting to a computer with a different regional setting because data might appear incorrect or mis- leading. Settings in the Format property for a field will override regional settings, so you can use the Format property to keep currency data consistent. Expressions and the Expression Builder Throughout an Access database, you will use expressions to perform a variety of operations. A formula you use in a spreadsheet cell to perform a calculation is one example of an expression. You will often use expressions in queries to define the criteria for the records you want the query to retrieve and when performing calculations and other operations in forms and reports. However, expressions are introduced in this chapter because you'll see a number of them as you work through exercises in the chapters ahead. Earlier in this chapter, we used the expression =Date() as the default value for the PurchasedDate field. This expression simply used the Date function to perform its calculation. An example of a more complex expression is one that can perform a calculation or return a result depending on whether a condition is true or false. For example, in the HelloWorld database, any campaign expense greater than $500 will require the employee managing the campaign to approve the expense. If the amount is less than $500, no approval is required. Here's an expression that would set the ApprovalRequired field to Yes if the amount spent is greater than $500. The expression uses the IIf function to define the conditions and the actions to take if the condition is true or false. IIf([AmountSpent] > 500, "Yes", "No") This function has three parts, each part separated by a comma. The first expression translates to the condition "If the value of AmountSpent is greater than $500." If that condition is true, the function returns the second expression, "Yes", which sets ApprovalRequired equal to Yes. If the condition is false (the expense is less than $500), the function returns the third expression, "No", which sets ApprovalRequired equal to No. We'll use expressions such as these in later chapters when we work in more detail with queries and forms. Expressions can contain the names of database objects, fields, and controls on a form or a report (known as identifiers), literal values (such as "Yes"), mathematical and logical operators, and func- tions such as the IIf function. A function returns a value based on the results of a calculation or other operation. In Access, you can use a number of functions that are part of the Visual Basic language, such as IIf and Date, which we've seen already, as well as Sum and Average, which you might have used in a spreadsheet. Operators are symbols used to perform mathematical, logical, and comparative tasks. Table 3-4 lists some of the operators that you can use in expressions.