Hack 48. Use a Custom Function in a Query
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.
5.10.1. 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.
5.10.2. 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. This first example assumes the format being imported is YYYYMMDD or YYMMDD. In this function, the string is brought in as an argument and the Left, Right, and Mid functions are used with the CDate function to create the date:
Public Function GetDate(Dt As Variant) As Date Dim MM As String Dim DD As String Dim YYYY As String If VBA.InStr(1, Dt, "/") > 0 Then Dt = "" Select Case VBA.Len(Access.Nz(Dt, "")) Case 8 YYYY = VBA.Left(Dt, 4) MM = VBA.Mid(Dt, 5, 2) DD = VBA.Right(Dt, 2) GetDate = VBA.CDate(MM & "/" & DD & "/" & YYYY) Case6 YYYY = VBA.Left(Dt, 2) MM = VBA.Mid(Dt, 3, 2) DD = Right(Dt, 2) GetDate = VBA.CDate(MM & "/" & DD & "/" & YYYY) Case Else GetDate = #1/1/1900# End Select End Function
Notice that this function passes the string as a variant; this allows it to test for a null value or trap an actual date. If the variable is declared as a string, a null value results in an error. In this case, if a real date, null value, or anything other than YYMMDD or YYYYMMDD is passed, it returns a date of 1/1/1900. You can set that date to be something else. The If InStr … Then line tests to see if a slash (/) is in the Dt variable. If it is, the procedure sets Dt to an empty string.
Now, let's assume you need to take it the other way. Assume you have a date field, and you need to turn it into a fixed-length string. Here is how the required function looks:
Public Function GetDateString(Dt As Date) As String Dim MM As String Dim DD As String Dim YYYY As String MM = VBA.Right(DatePart("m", Dt) + 100, 2) DD = VBA.Right(DatePart("d", Dt) + 100, 2) YYYY = VBA.DatePart("yyyy", Dt) GetDateString = YYYY & MM & DD End Function
This function passes the variable as a date. If a null or nondate is passed to the function, it returns #Error. This result simply shows how the function reacts when an inappropriate date is passed to it.
Also notice that the function uses the Right function along with DatePart and then adds 100 to it. This ensures that the month and date return two digits. If you didn't do this, it might work on your computer if you have dates set with leading zeros, but it could bomb on another computer. You can also use this logic anytime you need to put leading zeros in for a number. If you need five leading zeros, type right(x+100000,5).
Your next question might be, "How do I use these functions?" You can call these functions from within a query, just like you would any other function. You can also use them in forms, reports, and so on. Let's assume you have a table called tbl_PersonalInformation, and you have a field called HireDate that is a date type. If you need to have a field in a query that formats the date as YYYYMMDD, write it in the query's Design view, like this:
That is all you need to do. The date will be converted to a string in the format that you need. It is important to note that you could do the same thing the function does right in the query. However, by doing it that way, you have no way of easily reusing the logic elsewhere in the application.