Wednesday, October 15, 2008

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.

Tuesday, October 14, 2008

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
Another one from the code archive at, 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**

Monday, October 13, 2008

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.

Sunday, October 12, 2008

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 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.

Sunday, September 14, 2008

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.

Saturday, September 13, 2008

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

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.


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 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.

Saturday, March 29, 2008

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.

Tuesday, March 18, 2008

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.

Sunday, March 16, 2008

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

Friday, March 14, 2008

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.

Thursday, March 13, 2008

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.

"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.

Tuesday, March 11, 2008

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.

SQL Tutorial From

If you work in Access you need to know SQL, but where do you go to learn it? Here's a tutorial from 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!

Sunday, March 9, 2008

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

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:

Thursday, March 6, 2008

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.

Sunday, March 2, 2008

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.

Thursday, February 28, 2008

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.

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.

Wednesday, February 27, 2008

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:

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

Sunday, February 24, 2008

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.

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.

Saturday, February 23, 2008

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:

Friday, February 22, 2008

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:

Some non-Microsoft web links:

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 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.

Thursday, February 21, 2008

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.

Monday, February 18, 2008

Microsoft Office 2000/Visual Basic Programmer's Guide

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.

Sunday, February 17, 2008

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 is really neat. It demonstrates a report popping up a form to collect criteria from the user.

Friday, February 15, 2008

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
      (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.

Thursday, February 14, 2008

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.

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
  (Clients.ClientCode Between "BC" And "BZ")   
    (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.

Wednesday, February 13, 2008

A Working Treeview Example with Drag and Drop

Here's another working treeview sample from 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.

Tuesday, February 12, 2008

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)

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!

Monday, February 11, 2008

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:

The example uses a treeview to select books or authors to display information about.
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.

Sunday, February 10, 2008

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!

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.

Saturday, February 9, 2008

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!

Monday, February 4, 2008

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.

Saturday, February 2, 2008

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:
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:
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**

Thursday, January 31, 2008

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....

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)

Sunday, January 27, 2008

Autonumber Your Records Without Using the Built-In Autonumber

"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.

Thursday, January 24, 2008

Try My New Swicki!

I just signed up for a Swicki at 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")

Sunday, January 20, 2008

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

Saturday, January 19, 2008

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:

** link cleaned up 2011-09-05 **
normalization, Database design,

Friday, January 18, 2008

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!

Thursday, January 17, 2008

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.

Wednesday, January 16, 2008

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.