Two Functions You Don’t Use Enough

Wayne Wallace shows you how to use two functions (Eval and Format) to do things that you might not have thought of.

One of the great things about working in the Access environment is the number of functions that are built into VBA. The only problem is that there’s no central repository for VBA functions. It would be convenient if there was someplace where you could just type a period and get a dropdown, IntelliSense list of VBA functions. Until that function comes along, here are two functions that you may not have heard of or, if you have heard of them, two functions doing things that you might not have thought of. Continue reading

Posted in VBA | Leave a comment

The FileSystemObject

Sometimes your data isn’t a database; sometimes you need to get data from a flat file. Or maybe you need information about your users’ files. Chris Weber shows how to solve all of these problems with the FileSystemObject.

Have you ever needed to manipulate files from within Access? Or get directory information? Or just extract the extension of a file name that your user chose through an Open File dialog? If you have, you’ve probably used the traditional file I/O statements native to VBA. For example, the simple code snippet that follows uses VBA file statements to open a file for writing, writes strings to the file, closes the file, reopens it, reads the strings back to the Immediate (Debug) window, and finally deletes (Kill) the file: Continue reading

Posted in VBA | Leave a comment

SubQueries to the Rescue

One of SQL’s more useful features is its subqueries. Peter Vogel hunts them down for you in this article and shows how to get some enormous performance gains. He even bags the rare and powerful correlated subquery.

In an earlier article of my SQL series, I mentioned that Access doesn’t directly support one of SQL’s more powerful features: subqueries. In this article I’ll defend that point of view because, as it has been pointed out to me, you can create subqueries in the Access query design window. I’ll also show you how to use this feature to speed up your programs while eliminating code. Continue reading

Posted in VBA | Leave a comment

Stupid Date Tricks

In this article, Doug Steele demonstrates a useful formula to use with date calculations.

If I’ve got a date, can I determine the date of the Monday for the week that date is in? For instance, I’ve got a date of 17 November 2004 (which is a Wednesday), and I want to identify that the Monday of that week is 15 November 2004.

In my September column (which dealt with calculating working days), I alluded to how the Weekday function works, but I’m not sure I really spelled out the details. Continue reading

Posted in VBA | Leave a comment

Spatial Data, Part 3

In the final article of his three-part series on handling spatial data, David provides an application that makes use of the toolkit built up in our previous two issues.

In the previous two articles in this series (see “Accessing Spatial Data, Part 1” in the December 1997 issue, and “Accessing Spatial Data, Part 2” in the January 1998 issue), I described several methods for storing geographical or spatial data in an Access database, together with a number of functions for processing that data. In this final article, I’ll draw together some of the issues previously raised, using a forestry application as an example. And I’ll add some more functions to your toolbox of spatial spanners and wrenches. Continue reading

Posted in VBA | Leave a comment