Access 2010 How to write expressions for Web que

Today’s guest author is Russell Sinclair, the Program Manager for expressions, ACE, along with other features.

Access has traditionally supported a wide variety of expressions in different areas of the application. Tables and fields, queries, form and report properties, controls, and macros can all use expressions to evaluate data or logic to drive the behavior of an application. In the past, each of these contexts in which an expression is used have shared a single, common expression evaluation engine. This means that no matter where you use an expression, the functions and operators available to you will likely be the same. Things will change when you start to build web databases with Access 2010.

The architecture and access to data is fundamentally different when an application is running in a three tier environment. Control references, expressions and logic in the browser might have to make AJAX callbacks to a Web front-end, which calls a middle tier data cache, which then pulls data out of a backend server. The end result is logic in a form is constrained from utilizing optimizations inherent in the desktop version of Access. This new topology introduces changes to expressions and how they operate within Access and Access Services.

When an application is published to the server, expressions could be evaluated in JavaScript, the Excel calculation service, or using SQL Server, depending upon the context. You will find that many of the shortcuts you are used to using in client applications are not available in web databases.
Contexts
The expressions that can be used in web databases are dependent upon the context in which you are working. In all cases, the expressions available in web databases do not match the full suite of expressions that you can use in client objects. The following is a list of the main contexts in web databases, from least restrictive (the most expressions available) to most restrictive (the least number of expressions available).
Reports: This context covers all properties of reports and controls on reports that you can define in the report designer. Queries: This context includes all expressions in queries you create in the query designer in Access. This includes stored database queries, record sources for forms, reports, combo and list boxes, as well as the macro action arguments: In (in ForEachRecord and LookupRecord), Where Condition (in ForEachRecord, LookupRecord, OpenForm, OpenReport, BrowseTo and SetFilter) and Order By (in SetOrderBy). Data Macros: This context covers the expressions that you can use in the arguments of macro actions in the logic designer (except as noted in Queries). Tables: This context covers expressions in calculated columns, validation rules and default values. Forms and UI Macros: This context covers expressions used in form properties, form control properties and UI macros (except as noted in Queries).
Each of these contexts will provide a slightly different set of expressions you can use. It is important to know the various contexts only so that you attune yourself to what to expect in each situation. For example, the FormatDateTime() function is not available in the Tables contexts but is available in all other contexts.

The best way to know what expressions are available in the context in which you are working is to use the Expression Builder or follow suggestions from IntelliSense. Although the designers catch many of the unsupported expressions in each context, they cannot catch every issue. Chances are that if you can’t create the expression using Expression Builder or IntelliSense, it’s not valid for that context.

TIP: If you find that the expression you want to use is not available in the context in which you’re working, try other contexts to see if you can incorporate your expression at a lower level. For example, the Hour() function is not available in the Forms context but it is available in the Queries context. In this case, you might create a query for your form that makes use of the Hour() function and then consume the result in your form.
Strict Type Coercion
One of the most difficult changes for traditional Access users to get used to is likely to be the addition of strict type coercion on the server. Access client will allow you to compare values stored in mismatching data types. For example, users can create a query that compares the value of a date/time column to the string “2009-10-02” and they will receive results from the query appropriate to the comparison. However, the same query will fail on the server because the user is attempting to compare a string and a date value. The only exception to strict type coercion is between dates and numbers, mainly because they are both stored internally as numeric values. For all other data types, implicit coercion is blocked on the server.

Because of this restriction, there are two common design considerations you should keep in mind when working with different data types.
When you work with Boolean values, always use the constants True and False instead of the numeric values 0, -1 or 1. When you compare strings to numeric or date/time values use FormatNumber(), FormatPercent(), FormatCurrency() or FormatDateTime() functions to convert the field value to a string in order for the comparison to work on the server. Query Optimization and Bound Fields
There are a few potential issues you need to watch for when creating expressions for web databases.

When you apply a filter or sort to a form or report by running a macro action such as OpenForm, OpenReport or SetFilter, any fields you use in the restriction must appear on the form/report in an expression or field that is bound to a property of the object or controls on the object. For example, if you call OpenForm and use the WHERE Condition

[MyField] LIKE “A*”

you must ensure that somewhere on your form you have bound [MyField] to a control or expression (it can be hidden if you like). For performance reasons, Access Services optimizes queries to exclude fields that are not bound to the page. Unbound fields are excluded from the final output query, regardless of whether or not they are included in the query designer.
Fully Qualified Control References
Another consideration to keep in mind is that you should fully qualify control references wherever IntelliSense suggests it, rather than using shortened references. This means that you may have to use the control reference

[Forms]![MyForm]![MyControl]

instead of the shorter reference

[MyControl]

This is especially true when referring to controls in subforms. Subform control references should be fully qualified as

[Forms]![MyForm]![MySubForm].[Form]![MyControl]

rather than a relative reference such as

[MySubForm].[Form]![MyControl]

As suggested earlier, you should make use of the Expression Builder and IntelliSense whenever you can to be sure that the expression notation you’re using is supported. Typing fully qualified references is much easier now with the changes to these features.
TempVars in Queries
When it comes to queries, Access Services does not allow you to refer to form controls or TempVars in stored queries. For example, the following query is supported in standard databases but not web databases because it contains a reference to a form control (highlighted).



In order to make this query web compatible, you could add a parameter to the query using the Parameters dialog (available from the Query Tools, Design ribbon bar) called “prmPartNumber”. The restriction in the above query could then be rewritten as below.



IntelliSense will support you here in that when you start typing the parameter name in the criteria, the parameter will show up as a member of the IntelliSense.
Optional Parameters
One final issue to watch for is that some functions that take optional parameters require you to fill in some or all of the optional parameters in order to publish your application to the web. A good example of this is Iif(). In client databases, you can use the Iif() function and leave out the optional, third argument, such as

=IIF([MyField]>5, “Large”)

In web databases you will get an error that the expression is not compatible with the web. You will need to provide the optional third argument in order to save the expression

=IIF([MyField]>5, “Large”, “Small”)

Similar issues can be seen with other functions that take optional parameters, so you should watch for this possibility when you run into problems.
Conclusion
There will be a learning curve for expert Access developers to adjust to the challenges of an application that can run in the browser on a hosted service. After a while, you will get the hang of the constraints around expressions but until then, IntelliSense should be your new best friend.
<div