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.

Tuesday, January 15, 2008

Stephen Lebans' Site

If you haven't checked out Stephen Lebans' site you've really missed something. This site is the authority on the web for some types of solutions. It's a site every Access developer should know. You'll find downloadable working solutions ranging from zooming images in and out, to disabling the mouse scroll wheel, to a calendar, to creating PDF files. In particular I look to Stephen's site first for advanced topics related to forms and reports, especially interacting with the Windows API.

Monday, January 14, 2008

Relinking back-end tables

If you've split your database into front-end and back-end, you will from time-to-time need to update the links between the two. For me the most common reason for doing this is that I'm installing an update to the front-end. I build new releases in a development/test environment (where the front-end tables are linked to a test copy of the back-end), and when I'm ready, I copy a new version of the front-end to the server and update the table links so it's connected to the production back-end.

(If you haven't split your database into front-end and back-end and wonder if you should, see Sharing an Access Database.)

Relinking to the back-end tables manually can be time consuming and error prone. You'll probbaly want to adopt some automated way to do the relinking. Here are some that I've found.

Relink Access tables from code
This one from The Access Web is quite nice in that if it does a little hunting to find the table you need to link to. I haven't used it, but code from The access Web is always worth the time it takes to incorporate it into your database.

Relink tables from different datasources
An alternative version from The access Web, this one handles multiple back-ends.

Re-Linking Back-End Tables
This is from the Utteraccess code archive and includes a nice form to manage the relinking.

How to relink back-end tables with the common dialog control in Access 2000
I think this is the original article that the above posts mean to improve upon.

And now here's my version. It's simple and straightforward. It assumes the back-end will be in the same folder as the front-end and that all the linked tables will be linked to the same back-end. It looks in the custom properties of the front-end for the name of the back-end database (File/Database Properties/Custom, create a property called BEDBName.)

Public Sub UpdateTableLinks()
  Dim varThis As Variant
  Dim strThisFolder As String
  Dim strBEFileSpec As String
  strThisFolder = CurrentProject.Path
  strBEFileSpec = strThisFolder & "\" & GetDBCustomProperty("BEDBName")
  If MsgBox(Prompt:="Update remote table links to " & strBEFileSpec & "?", _
      Buttons:=vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
    For Each varThis In CurrentDb.TableDefs
      With varThis
        If Trim(Nz(.Connect)) Like ";DATABASE=*" Then
          .Connect = ";DATABASE=" & strBEFileSpec
        End If
      End With
    Next varThis
    MsgBox "Relink complete"
  End If
End Sub

Public Function GetDBCustomProperty(ByVal strPropertyName As String) As String
  GetDBCustomProperty = CurrentDb.Containers!Databases.Documents!UserDefined.Properties(strPropertyName)
End Function

Sunday, January 13, 2008

Tow Wikerath's Access Links Document

Just stumbled across this one while reading a post on UtterAccess. At an Access MVP named Tom Wickerath has posted a word document with a really rich mix of tips, helpful links, and a few how-tos.

Saturday, January 12, 2008

Nulls: why it's important to understand Empty or Unknown fields

An important concept to understand in Access is Nulls. A field's value can be null when it has not been assigned a value. I recently read an updated FAQA post on UtterAccess about nulls which opened my eyes to one aspect of working with nulls. So here's that article, plus some slightly more basic stuff that will be of more general interest

First some advice on working with null data in queries. This is important- many a novice user has got results different than he expected by ignoring the impact of nulls. See the following articles from Allen Browne:

This article at UtterAccess discusses some of the interesting aspects of working with nulls, especially with regards to concatentating strings. It also has links to a whole bunch of other articles on nulls.

Friday, January 11, 2008

Download the Automation Help File from Microsoft

Automation is the term used to describe one application using another to do its work- for instance Access using Excel to create a new spreadhseet and to put data from a into the cells of the worksheet. Automation is tremendously powerful, but can seem like black magic to the "uninitiated".

Microsoft provides references which help you get started with automation using Microsoft Office products. They are distributed as compiled help files. The most recent version of the help file is here.

Thursday, January 10, 2008

Getting Started with Treeviews: Posts from Microsoft

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 started with some Micsrosoft articles and found that was all I needed to build my first few treeviews. Here are the two I used plus one more I've found since that looks like it would have helped me a lot.

ACC97: Migrating from Data Outline Control to TreeView Control
How to fill a Treeview control recursively in Access 2000
How to use TreeView control Drag-and-Drop capabilities in Access 2000

As well as beginners' docs being a little scarce, the reference material can be hard to come by as well. Here's a link to the Treeview reference in the VB6 section of Microsoft's site.

See also: other treeview posts on my blog.

Wednesday, January 9, 2008

Collections and Object Variables: Watch Your Type!

Last week I had a perplexing problem with some code in one of my databases. In my code I was navigating up and down a hierarchical table and creating a collection of all the children or all the parents of a given record. All worked well until I tried to retreive the items from the collection, when I got Run-time error 3420: Object invalid or no longer set.

I've reproduced the problem with the following simplified code, which reads through an item table adding the item IDs to a collection and then listing the items in the collection:

Sub Test()
  Dim clnTest As Collection
  Dim rsData As DAO.Recordset
  Dim lngCounter As Long
  Set clnTest = New Collection
  ' read through tblItems and add the ItemIDs to the collection
  Set rsData = CurrentDb.OpenRecordset("tblItems")
  Do Until rsData.EOF
    clnTest.Add rsData!ItemID
  ' now list the data in the collection
  For lngCounter = 1 To clnTest.Count
    Debug.Print clnTest(lngCounter)
  Next lngCounter
  ' clean-up
  Set clnTest = Nothing
  Set rsData = Nothing
End Sub
Go ahead- try it- you'll get "Object invalid or no longer set" on the Debug.Print line.

What went wrong? Can you spot the problem? It turns out it's with the line clnTest.Add rsData!ItemID. Collections are always collections of variants. So when you add the ItemID field to the collection, it adds an object reference to the field itself. When you try to access it later, after closing the recordset, the object doesn't point to anything anymore. Hence the error.

What's the solution? You could change the offending line to: clnTest.Add rsData!ItemID.Value. Or you could cast it to the appropriate type, with something like clnTest.Add CLng(rsData!ItemID). Or you could use an intermediate variable with the appropariate type and assign the field to that variable and then add it to the collection.

Which is "better"? I'm not sure. I think I'd want to adopt a practice I could use all the time, not something that kicks in only when I'm using collections. Since best practive with VBA is to use Option Explicit and declare all your variables, and to be specific about any type conversions by using the appropriate conversion function, I guess maybe any time you convert out of a variant or an object which behaves like a variant, such as a field or a collection, you should use a type conversion function. (That would go double when you're putting the result into another variant.) In this case that might look like:

clnTest.Add CLng(rsData!ItemID)
Debug.Print CLng(clnTest(lngCounter))

Tuesday, January 8, 2008

Working Treeview Example

Treeviews are one of my favourite user interface components to develop with. But judging by the questions on the discussion forums, a lot of folk have trouble getting started with them. Here's a simple working example from the code archive at

See more treeview posts on my blog.

Monday, January 7, 2008

Maximum Value Function

In Excel I can write a formula like =Max(1,2,3). It correctly gives me 3 as the result.

In Access I can use Max in a totals query to find the highest value of a field across all the rows in a table.

But what if I want the highest value across three fields in a single row, or of three variabls in VBA. I can't use Max in that scenario in Access. Here's a user defined function that will work in both those scenarios:

Public Function GreatestValue(ParamArray varValues() As Variant) As Variant

    Dim varMax As Variant
    Dim lngCounter As Long
    varMax = Null
    For lngCounter = LBound(varValues) To UBound(varValues)
        If IsNull(varMax) Then
            varMax = varValues(lngCounter)
            If varValues(lngCounter) > varMax Then
                varMax = varValues(lngCounter)
            End If
        End If
    Next lngCounter
    GreatestValue = varMax
End Function
Paste the code above into a new standard module (not a form module) and you can then do in a query:
SELECT [A],[B],[C],GreatestValue([A],[B],[C]) AS HighestField FROM MyTable
or in VBA:

It should work with any number of values and with numbers or strings.

Sunday, January 6, 2008

Summing Times, or Date/Time Data: An Instant or a Duration?

The question on the forum goes something like this:

I'm trying to add these times together but when it gets over 24 hours it goes back to zero instead of going to 25. What am I doing wrong?

The Access date/time data type is well suited to storing an instant in time. A date and/or time when something happened. It's not well suited to storing durations- how long something took. For that you're better to work with a number. It could be a number of seconds, minutes, hours or days: whatever's meaningful in your situation. And you'll have to decide what field size is appropriate as well. My message is: intervals or durations should be treated as numbers, not times.

Here are some example queries. First our sample data- start and stop times for productions runs on systems:

SystemID   RunStartTime   RunStopTime
========   ============   ===========
   1        6:00:00 AM     2:00:00 PM
   1        3:00:00 PM    11:00:00 PM
   2        4:00:00 AM     6:00:00 AM
   2        8:00:00 AM    11:00:00 AM
   2        1:00:00 PM     2:00:00 PM
   2        3:00:00 PM    11:00:00 PM

Here's where many people start with something like this- they subtract the times:

  [RunStopTime]-[RunStartTime] AS RunLength
FROM tblRunTimes;
If you tell Access to format the RunLength as time it works pretty well. The times for each run work OK.

With our sample data it even works to sum the times per system:

  Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength
FROM tblRunTimes

But it falls apart when you sum the times for all the systems:

  Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength
FROM tblRunTimes;
gives you the following result:
What happened? The total of all the run times is actually 30 hours. But Access thinks it's a date and time. So it's actually one day later at 6:00 in the morning.

The answer is to use DatePart() to calculate each run length in seconds:

  DateDiff("s",[RunStartTime],[RunStopTime]) AS RunLength
FROM tblRunTimes;
then when you sum it you get total run time in seconds per system:
  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength
FROM tblRunTimes
GROUP BY tblRunTimes.SystemID;
or for all systems:
  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength
FROM tblRunTimes;

Treating the data as seconds will make your calculations work properly. You can sum, average, do stats, whatever you need. When you present information to the users you'll have to decide whether they want to see seconds or something else. To show hours instead simply divide by 3,600 (the number ofseconds in an hour). Here's an all SQL approach to break the total seconds down into days, hours, minutes and seconds:

  Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength,
  Int([RunLength]/(60*60*24)) AS Days,
  Int(([RunLength]-([Days]*(60*60*24)))/(60*60)) AS Hours,
  Int(([RunLength]-[Days]*(60*60*24)-[Hours]*(60*60))/(60)) AS Minutes,
  Int([RunLength]-[Days]*(24*60*60)-[Hours]*(60*60)-[Minutes]*(60)) AS Seconds
FROM tblRunTimes;

See also: How to store, calculate, and compare Date/Time data in Microsoft Access , More Date and Time Topics on MyMSAccessBlog.

Saturday, January 5, 2008

Embedding SQL in VBA

Often I find myself writing VBA code that creates SQL "on the fly". Most seasoned Access developers do this every day, but if you're new to this you may find this recent post at UA useful.

Friday, January 4, 2008

Formatting Cardinal Dates

Here's a neat one on cardinal dates. Cardinal numbers, you'll remember from school, are the "st's and th's", for example 1st and 2nd and 103rd. This article includes a function for 1st January 2008 that could easily e cumstomized for a variations like January 1st 2008.

Thursday, January 3, 2008

Automating Access Using Windows Script

I keep meaning to really learn to use Windows Script. It looks like a great addition to my toolkit. I stumbled across this article on Helen Feddema's Access Archon page. It looks like it gives a really detailed walkthrough of a sophisticated Windows Script solution.

Wednesday, January 2, 2008

TransferSpreadsheet: Specifiying the range or worksheet to export to

The help for the TransferSpreadhseet action says you can't do this, but that's not true. It says, in part:

TransferSpreadsheet Action

You can use the TransferSpreadsheet action to import or export data between the current Microsoft Access database (.mdb) or Access project (.adp) and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program. You can also link to data in a Lotus 1-2-3 spreadsheet file, but this data is read-only in Microsoft Access.


The TransferSpreadsheet action has the following arguments.



The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax does not work in Microsoft Access 97 or Microsoft Access 2000). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, Excel 8.0, or Excel 2000 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.

Note: When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

The last point is incorrect. You can specify a range when exporting. The following code will export the specified query to a specific worksheet within the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyWorksheetName"

And this example exports it to a specfic named range withing the workbook.

DoCmd.TransferSpreadsheet TransferType:=acExport, TableName:="Invoices", FileName:="c:\windows\temp\MyExcelWorkBook.xls", Range:="MyRange"

Tuesday, January 1, 2008

How To Query for Literal Special Characters in a Where Clause

Often in a query you'll want to compare the data in your tables to some text, either a constant or a variable in your VBA code. The query you'll ask Access to process for you will have your text surrounded by either single or double quotes. But what if your data might have quotes in it, such as the product description 12" Ruler, or the last name O'Holloran?

This Micsorsoft article discusses the problem and proposes tactics for dealing with it.