You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest author is Russell Sinclair, the Program Manager for expressions, ACE, along with other features. (NOTE: Because this post explains one of the basic improvements to Access 2010, it remains very popular among Access developers. For that reason, we're republishing it so many more of you can easily discover it.)
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.
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).
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.
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.
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*”
[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.
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
instead of the shorter reference
This is especially true when referring to controls in subforms. Subform control references should be fully qualified as
rather than a relative reference such as
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.
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.
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
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”)
=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.
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.
When I read articles like this, I am humbled. To sum up this article in one word? Spectacular Of most minor issue this in article is that ms-Word's autocorrect turned single quotes in the code expressions to into opening + closing quotes. I am doing some technical editing for a access book and turning off that feature in word is an good tip before you paste or edit any code expressions in word. Albert Kallal (Access MVP)
A very helpful post. Thank you. I notice that there is no expression builder in the VB Editor when writing VBA code. Up until Access 97 (I think), there used to be an expression builder, but it was removed in Access 2000 and it didn't return in subsequent versions of Access. Michael Kaplan created an add-in at www.trigeminal.com which did the same thing as the old Microsoft one, but (and I may have got this bit wrong) I don't think it worked with Access 2007.
Personally, I ensure all my forms and reports have HasModule set to Yes so I can use Intellisense, e.g. so I can write code like Form_frmMyForm.txtStuff = 2 but it might be handy to put the old expression builder back in 2010 so people can write code like Forms("frmMyForm")!txtStuff = 2
Thanks for this overview of changes/new features. I am experimenting with 2010 and this is very helpful.
With the rewrite of a stored query to use a parameter, how would the parameter value be associated with the form?
Jack, You have to set the parameter value via a macro:
Kind if neat to see that the query parameter setting will come back into use. I've not really touched that since the bug with query params in Access 2 (I think it was). Under some conditions, entering the query param in the dialog was the only way to get form references to resolve. Too bad I and my clients are not likely to ever use this since it relies on sharepoint.
Micheal - parameters are not specific to SharePoint.
Query parameters can be set when you call OpenForm, OpenReport or BrowseTo. When you call these functions from a UI macro, the logic designer has a link to Requery Parameters such that it will populate the parameter list from the underlying query. You can set the values of these parameters directly in the macro action to the value of a control on a form. In VBA, you can use the DoCmd.SetParameter call before calling OpenForm to set the values of the parameters to values you evaluate at runtime.
I haven't come across the FormatNumber(), FormatPercent(), FormatCurrency() or FormatDateTime() functions before. I've been using CBool(), CByte(), CCur(), CDate(), CDbl() from previous versions of Microsoft Access. Are there any specific guidelines on what we should use and when? (although there doesn't seem to be a specific replacement for CBool()).
The FormatXXX() functions are intended to convert non-string data to strings. They are similar to CStr() but you must explicitly specify the output format you expect form teh conversion. CBool(), CByte(), CCur(), CDate() and CStr() do not work in web databases. The only one of these functions that is supported is CDbl(). When converting numbers or dates, use CDbl() (since dates are compatible with numbers). When converting strings, use one of the above FormatXXX() functions. Boolean values do not have a direct conversion so you should use "Iif( >, True, False)" to convert your result into something that Access Services supports.
It looks like I was confusing two series of functions: The FormatXXX() functions are new versions of the older Format() function, as opposed to the type conversion CXXX() functions. Although I understand the Help content would not yet be finalised, I hope the help content will include: * for a function, whether it is web or client compatible, and
* a comprehensive list of web compatible functions.
Thanks for this again. A couple of images are not showing at present though in the Tempvars in Queries section.
Why are many functions available to sharepoint calculated columns not available in Access expression builder such as DateDif()?
I'm using access2010 on Sharepoint.
I have 2 combobox (1 = customer and the other for the contact)
When i change the customer i want to apply a filter on the content of the contact list depend of the customer.
I try to make a query with a tempVars but when i put the database on sharepoint
when the query is call i have a message : "The service is actually not availble"
What is the problem ?
Maybe there is another possibility to change the content of a combobox ?
Thanks for your help.