Access Hacks

Hack #48. Use a Custom Function in a Query

Write a custom function to manipulate multiple data formats.

When you need to perform complex manipulation of data in a query, it is often easier to write a function to perform the manipulation. You can avoid using complex functions inside a query and always write a user function. However, it is best to use your judgment. If you have a rather simple concatenation of a few fields, I suggest you write a user function within your query. But if you need to perform something complex and it is likely that you will need to do it in other places in the application, creating a new function will save you a lot of time.

Creating a New Function

To create a function, go to the Modules tab in Access, and create a new module. Once you are in the new module (you can also go into Design view in an existing module), select Insert → Procedure. Give it a name, select Function as the Type, and select Public as the Scope. Once you have your function, you can place variables between the parentheses. After the parentheses, give your function a type by typing As datatype; this ensures that your function is returned in the datatype that you expect.

Manipulating Dates

Dates come from different systems in many different formats, including YYYYMMDD, MM/DD/YYYY, and MMDDYYYY. The problem comes when you need to have the date in another format, as happens when you import data from a mainframe or a fixed-length text file in which the date is actually imported as text. ...