How Many Legs? An Exercise in Classes and Objects

So I ran across a puzzle about cats on a bus. It's here. As you'll see if you follow the link there are seven girls, each with seven backpacks, each with seven large cats, etc. The question is how: many legs in total?

As I looked at it it struck me as an interesting problem for objects and properties. If I created an Access database with a Backpack class and a Cat class and so on I could just instantiate a Bus object and then interrogate its Legs property.

Well it worked. It's not elegant, and there aren't a lot of comments, but it's an interesting exercise on classes and objects. You can download the database here.

More calendars (date pickers)

Here's another couple of links with calendars you can add to your forms.
An ActiveX-free pop-up calendar posted in the code archives at UtterAccess.com.
Another one from the code archive at UtterAccess.com, this one also ActiveX-free, with a large and small calendar you can incorporate into your forms.
And lastly a pop-up calendar from Allen Browne. Like the others, it's non-ActiveX ad can be easily imported into your database. Allen's page includes links to a couple of others if those are not enough.
See all my calendar form posts.


**Links cleaned up 2011-09-05**

Sample Access Databases From Office Online

I find that looking at a demonstration or sample database is a great way to learn more about Access. Microsoft has some great samples databases here on Micorsoft Office Online. that demonstrate some really neat techniques for forms, reports, and queries.

Read more »

Appointment Calendar From the UtterAccess Code Archive

A lot of questions a posted on discussion boards about building forms with embedded calendars. Some are just to select a date, some are to show information on a calendar. This post in the code archive at UtterAccess.com is a neat working example of an appointment calendar, with both a monthly view showing all appointments in a month and a daily view showing appointments in time slots. All accomplished without ActiveX controls!
See all my calendar form posts.

My Treeview Project | Episode Five: Using The Treeview To Select Records to View/Edit

In episode 5 we'll make the treeview really do domething. We'll use it to select records from the products and categories tables that we want to see or edit. The work you'll have to do in this episode is a little more than in the last two, but we'll go through it step-by-step. We're building on the work you've done in previous episodes.

Read more »

File and Folder Dialogs: Even More Sophistication

Here's another version of "how do I prompt the user for a folder name/file name? This one adds additional sophistication. On the folder dialog you can pre-select a folder. On the file dialog you can specify the X and Y coordinates for the dialog box. It's at http://www.lebans.com/callbackbrowser.htm.

See also How to display and to use the File dialog box in Microsoft Access (Oct 2007).

Adding a Calendar to Your Froms: Stephen Lebans' Code with Detailed Instructions by Jeff Conrad

How do I put a calendar on my form so users can pick dates? There are so many different ways to do this. Here's one that has the benefit of relying on no activeX controls- which makes it more portable and more distributable. It does that while still providing a really slick interface because it uses APIs rather than just text boxes on forms.

In this case Jeff Conrad has written detailed step-by-step instructions for implementing Stephen Lebans' solution. You can never go too far wrong with Stephen's solutions, and Jeff's instruction make it much easier to implement.

See all my calendar form posts.

List of programming resources for Visual Basic for Applications

Take a look at this article from Microsoft. They've pulled together a whole pile of resources that will be useful for the VBA programmer. If you're getting started with VBA, or are just looking for more VBA references and resources, this looks like a great page to check out.

Quote:

This article lists some of the resources that are available to help you learn Microsoft Visual Basic for Applications programming. The article contains the following categories:

• Online Help
• The Object Browser
• Microsoft Press and third-party reference and learning materials
• Microsoft Knowledge Base on the World Wide Web
• Helpful Knowledge Base articles
• Internet newsgroups
• Other Web resources

Microsoft How-To: Removing a Subdatasheet

Subdatasheets are a neat feature added to Access in the 2000 version, but for anyone building applications, especially when deploying over a network, they should probably be turned off. You'll use your own forms instead of this "free" database feature, and they can cost you in terms of performance.
This article shows how to remove them in Access 2007. In 2003 the remove option is under Format/Subdatasheet.

Also see this great UtterAccess post by Crystal at UtterAccess.com on Subdatasheets, which includes links to articles that discuss the performance issue.

**Links cleaned up 2011-09-05**

Getting the path to windows special folders

Sometimes your VBA program needs the path to the users desktop, or to a temp folder where working files can be placed, or to the user's documents folder. There are several ways to do this.

There's some great API code here at The Access Web.

You can also do it using the Windows Scripting Host. That method, as well as another API method, is described here, in a previous blog post. That post talks specifically about getting the path to the temp folder. I can't see that choice within the Access Web code above. Perhaps no one approach offers all the special paths. In the previous blog post linked earlier in the paragraph I use GetSpecialFolder() to get the temp folder path. You can get a couple of other special folders with that method as well- see the Microsoft documentation for details.

My Treeview Project | Episode Four: Expand All and Collapse All Buttons

In episode 4 we'll take the treeview we designed in episode 3 and add buttons for "collapse all" and "expand all". This will let us get started working with the nodes in a treeview.

In previous episodes I told you to make sure you'd built the treeview from the last episode so we could add to it. This time you can download the completed treeview including this episode.

Read more »

Preventing Page Breaks In The Middle Of Your Subreports

Today I was working with a large report with six or seven subreports. Since the subreports are all set to "grow" the page lengths vary and sometimes the page breaks come where I don't want them.

ACC2000: Keeping Subreport or Text Box Data Together on a Page shows how to solve this by creating artificial report sections.

Office VBA and the Windows API

Here's a great overview of using the Windows API with VBA applications: Office VBA and the Windows API

**Edit Sept 16 '08: The link above doesn't seem to work anymore, but I think the same article is at http://msdn.microsoft.com/en-us/library/aa201293.aspx.

Programming combo box and list box controls in Microsoft Access

Here are a couple of posts from the MSDN archive. They had been taken out of general circulation, but have since been reintroduced to the library. Together they make up one of the most comprehensive reviews of developing with combo boxes and list boxes I've seen.

Programming Combo Box and List Box Controls in Microsoft Access, Part 1
Part one reviews the various options for supplying a list of values to a combo box or list box (all but one), including one I didn't know existed, the Field List setting. It also covers off how to find out what the user selected, including in a multi-select list box. It talks about using the NotInList event to make your lists dynamic. The only thing I can see missing is cascading combo boxes, which I cover off in these posts.

Programming Combo Box and List Box Controls in Microsoft Access, Part 2
Part two goes into using a function to provide values to the list. This one's a little more complex, but really not difficult, and provides functionality the other options don't.

Demo: Use the Expression Builder

I think the expression builder is an under-used tool in Access. Microsoft has put up a demo that shows what it can do. See Demo: Use the Expression Builder.

Quote:
"You can use expressions throughout Access — for example, to calculate values, act as query criteria, or validate data. The Expression Builder helps you create expressions by providing easy access to the names of the fields and controls in your database, and to many of the built-in functions that are available to you when you write expressions. This demo shows you how to use the Expression Builder for one common task —calculating a date value on a report."

My Treeview Project

Treeviews are really powerful interface components. I love building with them. They aren't easy to get started with, though, for a few reasons: they can't be bound to data so you have to fill them using VBA code, they're not part of Access and so aren't covered in the Access help (they're ActiveX controls), and they are often used with complex data, such as hierarchical relationships.

I see lots of questions on the web asking "How do I get started with treeviews- is there a tutorial?". My Treeview Project is just that- a tutorial. Seasoned VBA developers may find it goes a little slow, but be patient! It's aimed at folk for whom the Microsoft online references aren't enough. It goes step-by-step, starting with a simple "Hello World" treeview.

Here's the current table of contents.

Read more »

My Treeview Project | Episode Three: Changing How the Treeview Looks

Here's what we'll end up with
after this episode.

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In Episode 2: Northwind Categories and Products we created a treeview that displayed products in categories based on data from the Northwind sample database.

In this episode we'll take the treeview from epsiode 2 and add some tweaks that affect how it looks. This will let us look at a number of useful techniques and concepts. If you haven't done episode 2 you should go back to it now and build the database because you'll be working from there in this episode.

Read more »

SQL Tutorial From W3Schools.com

If you work in Access you need to know SQL, but where do you go to learn it? Here's a tutorial from W3Schools.com. What I like about the W3Schools tutorials is the "try it" box that, in this case, lets you type some SQL and see what it does. Neat!

Controlling a How a ListView Control Sorts Items Using Callbacks

Next to the Treeview, I think the Listview is my favourite control to program with in Access. Like the treeview, it's ActiveX. There's a set of pages about it in the MSDN library at http://msdn2.microsoft.com/library/aa443482.aspx.

I have a really neat treeview implementation in one of my applications. There's one user request I haven't been able to satisfy, though: sort by price. I have a column for price and one for description (plus several others.) I have it set up so that when you click a column header it sorts by that column. But under the covers the sort is always alphabetic, as the control treats all the values as text. So $11.00 sorts ahead of $2.00.

I wasn't looking for a solution for that this afternoon when I stumbled across this article:

Read more »

Filtering Using Multi-Select List Boxes

Another really common Access question goes like this:

• How do I use a multi-select list box to filter my report?
• or How do I let my user select multiple items and then open a form or report showing those items?

List boxes allowing multiple selections are a pretty natural user interface. Think "print a report for three different departments" or "include these four employees in the sales graph". Creating and using criteria for your form or report with a multi-select list box is a lot different than a combo box or single select list box, but it's not hard at all once you've seen it in action and done it a couple of times.

Read more »

Lookup Fields- Good or Bad?

Lookup fields in tables can be really convenient for casual users. But most serious users agree that the disadvantages outweight the benefits. Here's an article on The Access Web outlining the disadvantages.

How to Synchronize Two Combo Boxes on a Form in Access

"I have two combo boxes (or list boxes) on my form. When the user picks an items in one list I want the choices in the second list to be narrowed down".

This remains one of the most frequent questions Access database users run up against early in their work with Access forms. It is sometimes referred to as cascading combo boxes, or dependant combo boxes, or synchronizing combo boxes.

Read more »

Video How-To: Make a Query Ask For Input.

Take a look at this Video How-To: Make a query ask for input. It's a great first place to start if you want to learn how to have queries prompt for input, or use a form to provide the criteria for a query.

Quote:
Want to make your query ask for input when it runs? Watch these demos to see how you can add parameters to a query, handle input that is the wrong kind of data, and use a form to collect parameters and then use them with multiple queries.

Help Access run faster

The Microsoft Office Online team has posted a new rundown on performance considerations for Access here. Some of the tips are also in other articles I've linked previously, but this looks like a good list.

Coding Techniques and Programming Practices

Here's a Microsoft article with some leading practices for developers.

Coding Techniques and Programming Practices

The table of contents:

• Coding Standards and Code Reviews
• Coding Techniques
• Best Practices
• Conclusion
• Suggested Reading

Access/Outlook Automation

Here's a good looking article from Microsoft on automation between Access and Outlook: Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003

My Treeview Project | Episode Two: Northwind Categories and Products

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In this episode we'll build a treeview to display products in categories.

We'll use data from Microsoft's Northwind Traders sample database. If you don't have a copy you can download it here .

Start by creating a blank form in the Northwind Traders database and add a Treeview control, the same way we did in episode 1.

Read more »

MS Project- Use the TaskTable to Get the Fields Project Displays



If you're building an application that uses data about tasks in an MS Project project plan, you may simply need all the properties of all the tasks, or you may wish to use the data as it's currently displayed in MS Project. A task has dozens of pieces of data associated with it; only some are displayed in the current view.

Read more »

MS Project Automation Links

MS Project is part of Microsoft's Office family and so behaves like the other Office tools in terms of providing a rich object model for VBA development. I've built integration between Project and Access and between Project and Excel. Here are some links you might find useful if you're considering Project automation:

Read more »

Microsoft Controls Reference

Here's a link to keep handy: ActiveX Controls Reference on MSDN. It's written for VB and Visual Studio, rather than for VBA and Access, but the differences between the two are not important for this purpose. At least in my environment there's no help loaded for ActiveX controls such as Treeview, Listview, and DateTimePicker, so I use this reference to the properties, methods and events of these controls.

How do I let my user pick a date from a calendar?

There are tons of ways to do this- some use ActiveX controls, some do not. Some have more functionality than others.
For a simple approach, the DateTimePicker ActiveX control is probably your best bet. The only disadvantage is that it's an ActiveX control, which can be difficult to manage if you have to install in a variety of environments. Using the DateTimePicker is really just like using a combo box. Use it bound or unbound. Just like a combo box, when the user clicks the arrow the control shows the list of choices- in this case a calendar where the user can click on a date.

Some Microsoft links:

• A walkthough of using the Access Calendar Control
• A reference for the DateTimePicker ActiveX control
• A reference for the ActiveX MonthView control

Some non-Microsoft web links:

• A nice round-up on UtterAccess.com of several good solutions
• A Pop-up Calendar for your Access Forms (Tutorial using the Access Calendar Control)

Demo: Bring your business data together in an Access database

This is really an introductory overview of Access. It would be a good place for a brand new user to start. If you're think you might need to use Access, or you're wondering if you've outgrown Excel as a data store, this would be a good place to start. Demo: Bring your business data together in an Access database.

How do I resize my text box to fit my text?

You can set the Can Grow property of your text box to Yes all you like, it won't grow on your form when it's displayed. Can Grow only applies when a form or report is printed. So how do you make your text box adjust its size to exactly match the amount of text you're displaying?

Take a look at TextHeightWidth.zip on Stephen Lebans' site. It's actually pretty easy to implement and allows you to find out how big your text really is and to adjust the text box (or combo box or list box) accordingly.

Excel: Using Custom Functions in Dynamic Ranges

This is neat. Define ranges in Excel not as a static group of cells but using a function which will dynamically determine which cells to include.

Another Treeview Reference from Microsoft

I stumbled across this post on MSDN this morning. It gives a great overview of the treeview control that I hadn't seen before.

See also: other treeview posts on my blog.

Microsoft Office 2000/Visual Basic Programmer's Guide

Quote:
The Microsoft Office 2000/Visual Basic Programmer's Guide describes how to use the applications and components in Microsoft Office 2000 to build custom solutions. This guide is the definitive resource for developers who are creating custom solutions based on Office applications. From the overall design of your solution to the nitty-gritty details of implementing it, this book tells you what you need to know to get things done quickly and productively.

This is a great resource for Office VBA developers. Among other valuable chapters it includes one of the only references I have been able to find for custom classes and objects.

Use a Pop-Up Form To Filter Your Report

How do I use a form to specify criteria for my report using a form? There are a number of ways to do this. This post in the code archive at UtterAccess.com is really neat. It demonstrates a report popping up a form to collect criteria from the user.

Read more »

Subquery: Add Missing Master Records

Let's say you have some data to add to your detail table, but some of the master records are missing. Here's a example query that uses a subquery to add "missing records" based on matching an ID field between two tables.

INSERT INTO tblMasterRecords ( MyID, MyText )
  SELECT MyID , "New Master record"
  FROM tblNewData
  WHERE NOT EXISTS
      (SELECT * FROM tblMasterRecords WHERE MyID=tblNewData.MyID);

Some things to note here:

• The query in the brackets is called a subquery.
• The EXISTS condition resolves to true when the subquery returns any records. By using this in the WHERE NOT EXISTS syntax, the main query returns records from tblNewData only when the subquery doesn't return any records.
• Inside the subquery, the ID of current record in the main query is referenced as tblNewdata.MyID.

My Treeview Project | Episode One: The Hello World! Treeview

The hello world program is a common first exercise when learning to work in a new programming environment: a program that simply displays or prints "Hello World!". In the first episode of this new series on treeviews we'll build the hello world treeview in an Access form.

Read more »

If My Filter Doesn't Find Any Records, Show Me All the Records Instead!

Here's a neat little SQL subquery. The request was: If no records match the specified criteria show me all records instead. I think it was to be used in a form where the user would be able to browse the data. They'd use keyword to subset the data, but the design point was to never not show any records.

This can be done using the Exists keyword. Here's an example:

SELECT Clients.*
FROM Clients
WHERE
  (Clients.ClientCode Between "BC" And "BZ")   
  OR   
  (NOT EXISTS
    (SELECT * FROM Clients WHERE Clients.ClientCode Between "BC" And "BZ")
   ) ;

The EXISTS condition resolves to true when the subquery (inside the brackets starting with SELECT) returns any records. In this case I use NOT to reverse that. So if the condition WHERE Clients.ClientCode Between "BC" And "BZ" doesn't find any records, the criteria staring with NOT EXISTS is true, and so all records match the complete criteria (because of the OR) and the query returns all the rows in the table.

A Working Treeview Example with Drag and Drop

Here's another working treeview sample from UtterAccess.com. This one's pretty sophisticated, with a drag and drop function used to change who staff report to and another to drag unassigned orders from a listview into the treeview where you drop them on the staff that will work on them. A nice implementation indeed.
The author, Brent Spaulding, is one the the treeview experts at UtterAccess.
See also: other treeview posts on my blog.

Union Query Basics

Union queries are an important part of your SQL toolset that you may be overlooking. I ran across a two part overview of Union queries that would be a good starting point for anyone trying to understand how to use them, or even why they might be useful. Take a look at:

Microsoft Access Union Queries (Part 1)
Microsoft Access Union Queries (Part 2)

Quote:
Union Queries are used to bring together two recordsets of data to merge into one recordset of data. For instance, let’s say you have two tables, one for sales going to individuals, and one for sales going to companies. A union query can bring all of the records from both tables (providing you are querying the same number of fields) into one giant recordset so you can view all of your records at once.
This is useful because although you may want to keep tables separate because they may pertain to different departments, bringing them together into one big query will allow you to run different statistical numbers across all of your sales. You would easily be able to compare the percentage difference in corporate clients to individuals in any region, or perhaps see where your greatest individual sales base is in order to target corporations in the same area.
Union Queries can also be used to create a single source for a mailing list. Union Queries eliminate the need to create a make-table query in order to bring in some records, then an append query to add others on top which bloats the size of your database as you’re storing all this data twice – once in their own tables, and once merged in a new table – which means you’ll have to deal with deleting specific data or updating only certain data and creating new object after new object in your database.
Union Queries are just like other queries, they don’t take up the space of a table, and the query is always updated to reflect new table data. Just like other queries, Union Queries can be used for report record-sources as well! Great stuff!

Using a TreeView Control as a Record Selector

Treeview continues to be one of the most common of the search keywords that bring folk to MyMSAccessBlog. I love developing with treeviews and if I can narrow down the specific questions folk are searching for answers to I'll post some detailed tips or solutions. For now, here's here's a zip file from Helen Feddema's Access Archon page with a working example:

• original version circa 2002 with an article describing how it works
• new version from Feb 2008 with increased functionality including a neat "copy record" function

The example uses a treeview to select books or authors to display information about.

Quote:
A TreeView control makes a better record selector than the standard combo box for situations where you are working with hierarchical data, and want to allow selection of an item from a branch of the data hierarchy.

For all my treeview posts click here.

How Should I Structure My Tables for Questionnaires or Surveys

Scenario: casual database developer has built a database for surveys or questionnaires and can't figure out why the queries are becoming so cumbersome.

Turns out there's a right way to design a database for this kind of work, and it's nothing like you'd do it in Excel. Here's an article from Microsoft that describes the right way to manage data like this.

Calculating elapsed time is more than just numbers

Here's a neat post on the Access Team Blog. It's a function you can use in your application to display elapsed time in a more "friendly" way. The units used and the precision applied are determined by the magnitude of the interval- take a look!

Quote:
I didn’t just want to show the hours or even days elapsed, but something more in sync with the way I want the information given to me- specifically that when dates are closer to the current time they are represented more precisely and dates that are farther away are shown generically.

***Update 2009-04-04: This article has also been posted on MSDN as: Constructing Modern Time Elapsed Strings in Access 2007.

Restore a Deleted Microsoft Access Table with VBA

I ran across this post: Restore a Deleted Microsoft Access Table with VBA which uses simple code to un-delete a table. I tried it in Access 2007 and it seemed to work fine. It looks like it relies on the fact that the table will still be in the tables collection until you close the database. It builds and executes a simple SQL query to copy from the (now invisible) table to a new table. Sounds like a good reference to keep handy!

Fixing AutoNumbers when Access assigns negatives or duplicates

I ran across this post from Allen Browne on resolving problems with autonumbers. It seems like a useful post to keep handy for when I need it someday.

In Access 2000 and later, an AutoNumber field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table.

Filtering Crosstab Queries

I was cruising UtterAccess this morning and came across a thread talking about filtering crosstab queries using parameters. I remember having trouble with this once but had not really needed it lately so hadn't looked into it. The solution in the thread pointed back to two ohter UA posts with the answer:

• I have a report pulling data from a crosstab query. I want to add a date range criteria and also display the start and end date of the result.
• Need to transpose data...crosstab?

It seems it's as simple as specifying the parameter explicity in the query's parameter list. I did a little more reading and found two other good articles:

• ACC2000: Error When Running Crosstab Query with a Parameter (from Microsoft)
• Crosstab query techniques | Handle Paramaters (from Allen Browne)

The problem is summed up tersely in the Microsoft article as:

If you create a crosstab query that uses an implicit parameter [XXX] or a form reference in a WHERE clause (criteria), you may receive the following error message:

The Microsoft Jet database engine does not recognize '[XXX]' as a valid field name or expression.

And including your parameter or a reference to the form control in the query's parameter list solves it quickly and easily. Allen Browne's article has a step-by-step description of doing that.
Buried within one of the Access posts listed above is the phrase:

"You can use the Parameter names in the Report just like a Field in the RecordSource of the Report."

What's that all about? I tried it and it's as simple as that. It's redundant when you're filtering on a form control, but when you let the query prompt for the parameter you can use the parameter in your report just like a column in your recordsource. Really neat for listing the selction criteria in a report's header.
Nice- it's 7:40 on Saturday morning and I've learned two things already today!

**Some links above corrected 2011-09-05**

Use controls on your form to enter criteria for a query

This is a pretty basic technique. Have your query use text boxes or combo boxes on your form as its criteria for selecting records. This allows you to build forms where the user chooses the department to view sales for, or the region to view customers in, etc. Here are just a few of the links on the 'net that demonstrate this technique. By building your query this way and then building your report on the query you effectively filter your report as well.

Access: Filter a query based on a text box value in Access 2003/XP/2000/97
Question: I have anAccess 2003/XP/2000/97 database that contains a form called Orders. I want to be able to create a query that returns the currently selected record from the Orders form. How can I do this?
Answer: One way to write your query is to include a reference to the text box (or other object) that contains the value that you want to use as a query filter.... (more)

How To Use A Combo Box As Criteria For A Query.

Query By Form & Query By Where
This article focuses on a simple Classic Access Techniques called Query By Form and it is written with the idea that you don't need to write masses of code to build a clever Access application, ....(more)

Autonumber Your Records Without Using the Built-In Autonumber

Utter Angel's
MS Access Downloads

"I want my records to have a unique number assigned automatically, but I don't want to use the built-in autonumber."

First think through why you need to build your application that way. There are lots of arguments to be made that the autonumber is the best way to assign IDs, and that the primary key on your tables should be an autonumber. That being said, in one of my biggest databases I chose to generate the unique IDs in code instead of using an autonumber field. I was worried that if I needed to merge data between two copies of the database (for instance merging two separate projects) I would need to be able to change the ID number on the records before merging). I have still never merged data, but the database is working will with my "manual autonumber".

Here's a post from Candace Tripp's Utter Angel page with simple sample code for generating "next number in sequence" IDs without using an autonumber field.

Try My New Swicki!

I just signed up for a Swicki at http://www.eurekster.com/. I'm not sure what it's good at yet, but you'll find it on my sidebar, or right here:

Grouping records in a query by 15 minute intervals

I recently posted on Grouping records on date/time values in an Access report. But what if you want to group on time in a query? Here's an expression you could use in a query for grouping records into fifteen minute intervals. It uses DatePart() to get the minutes part of the time and then uses Int() and multiplication to cut off to the nearest 15 minutes. Then it uses DatePart again to get the hours part and sticks them together.

MyInterval: Format(DatePart("h",[MyTime]),"00") & ":" & Format(Int(DatePart("n",[MyTime])/15)*15,"00")

Grouping records on date/time values in an Access report

Access reports have some really powerful capabilities. This article demonstrates using the sorting and grouping functions to group reports on a range of dates.

reports, Date and time, grouping, group by month,group by date

Data Modelling

Good design of your database is really important to developing solutions that work, and that keep working. Here's a post at UtterAccess with links in turn to a couple of great references on data design, including a complete chapter on data modelling from the book Grover Park George On Access available for download. If you're building databases and you don't know anthing about data modelling, you probably should.
Additional reading:

• Database normalization, from Wikipedia, the free encyclopedia
• Description of the database normalization basics, by Microsoft

** Utteraccess.com link cleaned up 2011-09-05 **

normalization, Database design,

Rich formatted text that includes data from fields

Take a look at this post on the Access team Blog. It demonstrates some pretty neat use of the new Rich Text Format (which I think is new in Access 2007) combining field data with mark-up for formatting by simply concatentaing them together in the control's source. I can't wait to try it!

Count letters, words, sentences and paragraphs

Here's a nice little database in the code archive at UtterAccess with functions to count the number of letters, words, sentences and paragraphs in text.

Crosstab query how-to

Crosstab queries are really powerful. Like so many things in Access, there's a lot to learn to use them effectively. This article from Microsoft would be a great place to start if you were new to crosstabs.

I guess if I were new to crosstabs I might not know why I needed one. Here's an excerpt from the article.

When you want to restructure summary data to make it easier to read and understand, consider using a crosstab query.

A crosstab query calculates a sum, average, or other aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.), and then groups the results by two sets of values— one down the side of the datasheet and the other across the top.

Think a row per sales person and a column per month with total commission per person per month. Or average salary by pay band by division, with pay bands across the top and divisions down the left. This is the functionality Excel calls pivot tables.

Actually despite starting at the beginning, the article seems to cover most of the things you could need to know about crosstab queryies, including specifying the column headings you want, filtering, and handling nulls. The only thing it doesn't cover is reports based on crosstab queries, which get messy because the queries columns can vary with the data. I'll have to watch for a good article on that.