Many people are unaware that the result of a function can serve as an expression in a query or as a parameter to a query. The query shown in Figure 11.39 evaluates the result of a function called Initials. The return value from the function is evaluated with criteria to determine whether the employee is included in the query result. The Initials function shown here (it's also in the basUtils module of CHAP11EX.MDB, found on the sample code CD-ROM) receives two strings and returns the first character of each string followed by a period: |
Function Initials(strFirstName As String, _ strLastName As String) As String Initials = Left(strFirstName, 1) & "." & _ Left(strLastName, 1) & "." End Function
The return value from a function can also be used as the criteria for a query (see Figure 11.40). The query in the figure uses a function called HighlyPaid to determine which records appear in the query result. Here's what the HighlyPaid function looks like (it's also in the basUtils module of CHAP11EX.MDB, found on the sample code CD-ROM): |
Function HighlyPaid(strTitle) As Currency Dim curHighRate As Currency Select Case strTitle Case "Sr. Programmer" curHighRate = 60 Case "Systems Analyst" curHighRate = 80 Case "Project Manager" curHighRate = 100 Case Else curHighRate = 50 End Select HighlyPaid = curHighRate End Function
The function receives the employee's title as a parameter. It then evaluates the title and returns a threshold value to the query that's used as the criterion for the query's Billing Rate column.