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.
The Expression Builder sports new features and simpler UI in Access 2010. You will spend less effort thinking about syntax and available functions/properties because IntelliSense features provide all the information you need as you type.
The first thing you’ll notice about the dialog is that we used a little technique called progressive disclosure to focus the user experience.
Here’s the Access 2007 Expression Builder when editing the control source of a control (Acquired Date in the Assets template):
Here’s the Expression Builder for the same context in Access 2010:
Click the less button to collapse the catalog of expressions:
IntelliSense consists of a number of features that make expression creation easier. It helps you spend less time memorizing expression names and syntax. You also won’t need to learn a lot of keyboard shortcuts. Finally, you will spend less time hunting and pecking through documentation to find the expression information you need.
Access 2010 offers the following IntelliSense features:
IntelliSense is available in the Expression Builder as well as in other locations inside Access where you can type an expression.
Note: we do not display expressions in the Expression Builder if it is not supported in a context.
IntelliSense is available for both table Datasheet and Design views. We’ll explore both. Let’s continue with the Assets template as an example.
Open the Assets template and take a look at the Acquired Date and Retired Date fields. You will notice that they each have a validation rule that ensures the values are greater than January 1, 1900.
Here I am using the Expression Builder from the Ribbon in Datasheet View:
Now you want to make sure that Retired Date is always greater than Acquired Date. To do this, you create a table validation rule. When you type, IntelliSense will provide both functions and fields in the current table that match. Entries have icons that distinguish the two.
I can create a table validation rule in the Ribbon in Table Datasheet view. Alternatively, I can create it in the table properties task pane in Table Design view:
You can create queries faster and reduce errors by using IntelliSense in the query designer.
We’ll edit the Assets Extended query in the Assets template to add a constraint on the Retired Date field so only show assets that are in service:
The QuickTips in this example are particularly useful because they give you return value information so you can select the correct function.
You’ll receive IntelliSense to complete field names for tables included in your query:
When you show additional tables in the designer, you’ll receive IntelliSense to complete table names:
Finally, IntelliSense will list all parameters that you define for your query using the Parameters dialog in the Query Designer.
Now we’ll look at the experience of creating Depreciation (from the example in tables) as an unbound control. To do this, we open the Asset Detail form and add an unbound textbox called Depreciation. We then edit the Control Source for our textbox:
When you type the equal sign and a character, IntelliSense provides a list of the possible matches for my text. Because our context is inside a form, we have more possibilities. In addition to field names and functions, we also see the various properties from the form object model. When I put focus on a particular item, QuickTips provides help on that item.
Expression Build and IntelliSense filter out items that do not apply in the current context.
Now that we are generally informed about what IntelliSense has to offer, we will take a closer look at using IntelliSense in macros.
Access 2010 shows IntelliSense for macro arguments:
Embedded macros enumerates the list of controls and their properties for a given context.
The MacroError object is included in IntelliSense for UI and data macros. The most commonly used fields are MacroError.Description and MacroError.Number to help in debugging.
Updated: 8/11/2010 with updated images.
Though I am using Access for developing Applications since version 1.1, I always failed to utilize the expression builder. Nice to see the new collapsed view - but what is the difference towards pressing F2?
Looks great! On thing that I still sorely miss is the free MS Domain Builder Wizard that was available for Access97. I wonder if it could be added natively to the Access 2010 Expression builder... This series of screenshots show how it worked: www.aadconsulting.com/dmwiz.htm
I worked with Access 97 for years and never knew of that Domain Builder Wizard! There is another way of getting Intellisense when building domain expressions. I recently uploaded a free Access add-in to Utter Access which enables you to refer to items usually accessed as items in collections, particularly tables and queries, as objects and is called "SMOG" (Standard Module Object Generation). I've knocked up a quick video of how it allows you to use Intellisense when writing a DLookup expression. You can see the video here: screencast.com/.../BRaOolI9EO. It uses both SMOG and the fact that, if a form's HasModule property is True (either by being set manually or having had some code written for that form), you can refer to items on an open form using the Form_frmMyForm.txtMyTextBox format. SMOG itself can be downloaded from www.utteraccess.com/.../showflat.php and, because it allows you to refer to items as objects, you gain Intellisense and compile time checking of syntax, i.e. no more typos. Cheers, Alan
What would also be nice is if the Expression Builder remembered the last size it was, Why Oh why does it open so small? we aren't working on 640x480 anymore.
I am sure a lot of people don't know you can re-size it. Hey wow! It does remember the new size in 2010... well for the session it seems, Nice job. Better still if it was sticky between sessions/databases but hey good improvements! Bruce
I really like the new expression builder. . . I really do not use the domain buolder wizzard anymore, since is not available, however it was a great, great tool back in 1997. I that since you are focusing in new users now, and not too many power users, it will be great to add it all natively to the expression builder. But, i relly liked the new tool. Edwin
Sorry this is a bit off-topic, though it is Access 2010, but how do I run a native Access add-in in Access 2010. The add-in manager can be accessed via the general add-ins manager (along with COM add-ins) as before, but the ribbon access to the add-in manager seems to have disappeared and I cannot see anywhere to run an Access add-in once it is installed.
I've asked this question on the Connect site under Access 2010, but not had any response yet. I can't make sense of the ribbon customizer even though it suggests there is a Database Tools tab, but it isn't visible on my machine. Alan
While I'm very happy to see some significant improvement in Expression Builder, one long standing criticism I have of EB is that it does not consistently show what properties are actually available and what can't be used. For instance, in 2003, Parent property is not listed in the EB for a selected form but it is valid to use Parent in an expression to implicitly reference a parent form containing the form as a subform. OTOH, some properties are available in the list but are not usable or cannot be used in the context. If I can't rely on EB to consistently list what is actually available, it entails more work to guess if a property actually is available.
@Alan Cossey: What's the Connect site?
The Connect site is a site that Technical Preview testers can use. I got a reply to my post there about add-ins to the effect that MS know of the ribbon problem whereby it does not give access to add-in stuff. While they sort it out, I'll need to access that stuff via the Quick Access Toolbar. Presumably that goes for anything else that is missing from the current ribbons, e.g. the Linked Table Manager.
What I don't understand: why is the Query Grid still not updated? The part that needs most space (tables & queries), is the smallest, whereas the part that contains the fields needs endless rows where I need only a few (in case of criteria) - I always (!) have to resize the top half to get a better view of tables in there, stretching them, so I don't have to scroll down within each table. Arrrrggggggg!!! Another great addition would be a fast query editor - I create a SQL string in code, pick it up from the Immediate pane (after I stopped the code execution) and then paste it into a new query to view the results - it now takes 7 (?) clicks to view it as a regular query? There should be a much faster way to get this done. Why not some context menu option in VBE: 'SQL to Query' (once in debug mode, stepping through your procedure)?