Client managing own 401(k) needed custom application

We have some clients that use someone else to manage their 401(k) program. Still, they need the financial information from our system. For us this means that we generate a report with the customized fields.

To get this report, I've first had to figure out the SQL needed to query our SQL Server database. Then I've needed to figure out how to generate this SQL at runtime based on user input. Specifically, the user selects the "Check Date". The program makes this date the ending date in the date range and uses the first date of that month as the beginning date.

Once again, I'm using Microsoft Access with Visual Basic for Applications (VBA) to develop the interface to the SQL Server database (via pass-through queries), along with forms for the user.

Today I needed to look at the application again because the client needed their report for December 2006. I realized after generating it that I needed to make the program more robust and self-sustaining.

What I mean to say is that I'd set up the program to look at the "current year", which meant that, in January of the next year, it wouldn't get some information from the previous year. So I was able to tweak the code to generate the additional queries at runtime.

All a user sees is a calendar control on a form with a couple of buttons and some text. He or she selects the date, clicks a button, waits awhile, and receives a message that the report has been generated to a Microsoft Excel file. It's a very simple experience for the user, mainly because all the work is done behind the scenes.