Built-In Functions

As many of the previous samples demonstrate, Access 2000 has a rich library of built-in functions. They can help you speed up development of custom solutions by minimizing and simplifying the original custom code you need to prepare. You can use the built-in functions in the same manner that you use your own custom ones, in procedures, queries, forms, and reports. By familiarizing yourself with the potential uses for built-in functions, you can discover new and innovative ways to apply your own custom functions.

This section includes three samples that use built-in functions: one for a query, one for a form, and one for a report. You can, and probably will, merge all three approaches in your applications. The third example shows how to make a control on a report act as a source for VBA code in a report module by using built-in functions. All three samples work with a small invoice table and demonstrate ways to report on invoices that are past due.

Figure 1-22 shows the original InvoiceDates table on the left and a query showing a view of the table on the right. The query selects 2 of the 10 rows in the InvoiceDates table and computes a new column called Past Due Days. (The date of the screen shot is 9/12/98.)

Figure 1-22. An InvoiceDates table and the results of a query that selects rows and computes new fields with built-in functions.

You can find the current date using one of two built-in functions: the Date function and the Now function. (The return value of the Now function also includes the current time. If you are working with dates only, it doesn't matter which one you use.)

The query includes both columns from the original InvoiceDates table. The criterion for the InvoiceDate column is <Date()-30. Since the current date at the time of the query is 9/12/98, any invoice from before 8/13/98 is past due. Therefore, the query returns the invoices from 7/01/98 and 6/15/98.

Past Due Days merit their own column. This computed field uses a pair of built-in date functions: DateDiff and Now. The DateDiff function is ideal for computing the difference between two dates. You can specify the difference in days, months, quarters, years, and various other units of time. The DateDiff function for the Past Due Days column returns the difference in days. The expression that calculates the field is DateDiff("d", [InvoiceDate]+30,Now()). This expression determines the current date using the Now function (although the Date function would work equally well). The term [InvoiceDate] refers to the table, which acts as a record source for the query. The +30 designates the grace period during which an invoice is not considered late.

The query is a start for generating some useful business results, but it has a number of drawbacks. An obvious one is that Datasheet view does not tell you what the current date is. Therefore, you might wonder what the basis is for computing past due bills. The frmPastDueEvaluator form object in Figure 123 solves this problem by presenting the more user-friendly Form view of the data. In this view, three fields on the form provide the critical information. The Invoice Date field shows the creation date of the invoice. The next field shows the current date. The third field computes the number of days that an invoice is past due. This field displays a 0 if the invoice age is less than or equal to 30 days.

Figure 1-23. A form for computing the days that invoices are past due.

The last two fields on the form use built-in functions to compute their values. The Today's Date field uses Date. In this case, Date is better than Now because Now causes the date and time to show unless you explicitly format the field. If you set the field's Control Source property to =Date(), no formatting is necessary. The Days Late field uses an IIF (Immediate If) function. You write expressions with this function in a way that former spreadsheet developers will find very natural. For example, the expression for the last field on the form is IIF(Date()-[txtInvoiceDate]>30,Date()-[txtInvoiceDate]-30,0). The expression has three arguments, like the spreadsheet If function. The first argument evaluates to True or False; it is a condition. If the condition is true, the IIF function returns the value of the second argument; if the condition is false, the IIF function returns the value of the third argument. Therefore, if the date in the text box with the invoice date, txtInvoiceDate, is more than 30 days before current date, the IIF function returns the number of days the invoice is beyond the 30-day grace period. Otherwise, the IIF function returns 0.

The report in Figure 1-24 shows another approach to summarizing past due bills. The report includes three columns. The last column is a computed one that calculates the number of days an invoice is past due. For any given invoice row, this column is blank if the invoice is not past due. The report's header reminds the user of the report date so that the user knows what date defines the invoices as past due.

Figure 1-24. A report that conditionally shows the number of days that an invoice is past due. If the invoice is less than or equal to 30 days old, the last column is blank.

A formula is used to compute the last column in the report. You should be able to write the formula based on the previous two samples. What makes the report special is that the last column is sometimes blank. The OnFormat event procedure for the form's Detail section makes this possible by conditionally manipulating at run time the Visible property of the control for the report's last column. The event procedure logic is as follows:

 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Dim ctlD As Control     For Each ctlD In Me.Detail.Controls         If ctlD.Name = "txtPastDueDays" Then             If ctlD.Value >= 0 Then                 ctlD.Visible = True             Else                 ctlD.Visible = False             End If         End If     Next ctlD End Sub 

The event procedure uses a For Each…Next statement to loop through the controls for each record, looking for the txtPastDueDays text box. This text box contains the number of days that an invoice is past due. If the quantity is positive, the invoice is past due and the code sets the control's Visible property to true. Otherwise, the invoice is not past due and the code sets the control's Visible property to false. These few lines of code generate the flexible report formatting at run time.

Summary of Selected Functions

Access 2000 has over 170 built-in functions that perform a broad range of tasks, including conversion between data types, date/time processing, statistical analysis of content in tables and queries, mathematics, and text processing. You can learn about the functions by first developing a general appreciation for what they do. This can save you from writing new code for a task when Microsoft has already written the code for you. Next, you can try to decipher how the functions complement one another. You can also learn the characteristics of the return values. For example, the Hex function returns a string representing the hexadecimal equivalent of a decimal number, and the Oct function performs a similar conversion to an octal representation. The output of both functions is suitable for string concatenation and comparisons but not for arithmetic operations.

You can also explore the impact of a function's optional arguments on the function's return value. Failure to understand these arguments can sometimes lead to misleading or confusing results. For example, the StrComp function compares two strings. The first two arguments are the strings to be compared. A third, optional argument specifies the type of comparison that is to be performed on the strings. You can specify a case-insensitive comparison, a case-sensitive comparison, or an alternative based on the New Database Sort Order option on the General page of the Access 2000 Options dialog box. If you do not specify this optional comparison, the function falls back to the comparison method specified by the Option Compare statement in a module's Declaration area. (If no method is specified, Access uses a binary comparison.)

The following table shows a selection of function categories and describes the purpose of selected functions in each category. There are more categories than those shown in the table, and there are generally more functions in each category. Any categorization is arbitrary since many functions can fit into more than one category. For example, the DateSerial function processes dates by returning date serial numbers based on non_date/time arguments, but it can also convert string dates to serial numbers. It therefore fits into both the date/time and conversion categories.

Selected Function Categories

Function Category	Representative Functions	Purpose
Conversion	CDbl, CSng, CInt, CCur, CDec, Str, Val, Hex, Oct	Determining the type of arithmetic an expression does and converting between data types
Date/time	Date, Now, DateAdd, DatePart, DateDiff, Year, Month, Day, Hour, Minute, Second, Weekday, DateValue, DateSerial, MonthName, WeekdayName, FormatDateTime	Expressing, computing with, and extracting date and time values from date serial numbers and string representations of dates and times
Domain aggregate	DLookup, DCount, DSum, DAvg, DVar	Computing statistical results, such as the count for a domain (such as a table or a query)
Error handling	Error, CVErr, IsError	Error trapping and custom error codes
Inspection	IsDate, IsTime, IsNumeric, IsNull, IsEmpty,VarType	Programmatically assessing data types
Math	Rnd, Sqr, Exp, Log, Sin, Cos, Tan	Performing mathematical calculations
Messages	MsgBox, InputBox	Issuing messages and gathering input from the keyboard
Text	Left, Right, Mid, Trim, UCase, LCaseStrReverse, Replace, InStr, InStrReverse	Processing strings
Miscellaneous	Sum, Count, RGB, FV, NPV, CreateObject, GetObject, SysCmd	Aggregating statistical results, setting color properties, computing financial results, creating or getting references to ActiveX objects, presenting progress meters, and more

These functions perform a variety of tasks. String functions can extract, transform, and truncate unwanted parts of a text string, but you can also use the built-in functions to calculate financial results or mathematical values. Inspection functions are useful for determining data types before you attempt to compute expressions. Wise use of these functions and the conversion functions can enable your code to branch around potential data type mismatch errors. (Error processing is an essential part of any complete solution. Without it, your solutions cannot trap run-time errors or raise custom run-time errors.) Domain aggregate functions are not very fast, but they can replace a whole query or a more complicated SQL statement. For example, your applications can use the domain aggregate functions in VBA procedures as well as in queries and forms. SQL aggregate functions, such as Count and Sum, can also generate statistical results for record sources. These functions are not as widely applicable as domain aggregate functions. For instance, you cannot use them in VBA procedures.

Figure 1-25 shows a procedure and the Immediate window to illustrate the behavior of the Hex function. The procedure initially transforms both arguments to hexadecimal strings and then prints the results in the Immediate window.

Figure 1-25. A procedure that tests the Hex function.

The string returned by the Hex function is not appropriate for arithmetic. The hextest function works around this by performing arithmetic with its decimal arguments and then transforming the result with the Hex function. The next several lines of the procedure demonstrate this approach. They add firstarg and secondarg and save the result in newarg. Then the procedure transforms newarg to a hexadecimal string and stores it in newhex. The procedure then prints newarg and newhex in the Immediate window.

The last transformation in hextest converts decimal 90 to the hexadecimal equivalent of 5A. The comment lines before the transformation indicate that 90 is 5 * 16 + 10.

The last pair of lines in the Immediate window show that the Hex function returns string values. Actually, Hex returns a Variant data type, but the Variant's subtype is a string. The arithmetic operation hex 16 + hex 16 should yield hex 20. However, the Immediate window returns 1010. This shows that the plus sign in the newarg = firstarg + secondarg line performs a string concatenation instead of a numerical sum.

You should always look for techniques that minimize processing time. This is especially important for solutions that have a long lifetime of use or that perform essential business tasks. Figure 1-26 shows a procedure that can serve as the basis for timing the performance of your code. It computes the time that it takes to run a loop a fixed number of times. You can extend this basic design to compare the time it takes to perform a task with two different coding solutions. As you can see from the Immediate window, the simple code block runs quickly. On my test computer, it took just 2 seconds to pass through the loop two million times.

Figure 1-26. Timing the completion of a task. This TimedLoop procedure also shows how several built-in functions operate.

The TimedLoop procedure accepts a single argument that specifies how many times to run its loop. You can often discern performance differences better if you run a loop for a reasonable number of iterations. This simple loop has just two short statements. Note that before the procedure starts the loop, it saves the current time (Now) in dtmStartTime. After all the iterations have completed, the procedure saves the completion time in dtmEndTime. The DateDiff function computes the difference between these two values in seconds. (You previously saw this same function compute the difference between dates in days. It works with many time units, including years, quarters, hours, weeks, and even weekdays.) The DateDiff function handles the conversions to seconds and any peculiarities that arise from passing through midnight, and extracts the relevant quantities from the start and end times.

Since this particular timing test uses such a large number of iterations, it is particularly convenient to express the number of iterations with commas. The use of the Format function reveals how to accomplish this.

The domain aggregate functions are another distinct class of functions. They return statistical and lookup information about a domain (a set of records). The records can reside in either a table or the result of a select query. The functions can return count, sum, average, variance, and standard deviation statistics about a domain. You can also use the domain aggregate DMin and DMax functions to return the smallest or largest value from a column in the domain. The DFirst and DLast domain aggregate functions return a random record rather than the first or last record from the records sorted on one or more fields. The DLookup domain aggregate function is very popular; it returns one or more records that match a criterion. All domain aggregate functions share a format similar to the one shown here:

 DFunctionName("fieldexpression", "domainname", "criteria") 

All domain aggregate function names begin with D. Notice that the syntax embraces all three arguments in quotes. You can optionally place brackets around the field name in the first and third arguments. The first two arguments are mandatory, and the third is optional. The fieldexpression argument names a field on which to report. The domainname argument is the name of a record source. The optional third argument specifies a criterion statement for designating elements that you want in the return set. To return the company name from the first record in the Shippers table from the Northwind database, you use the following DLookup function:

 DLookup("CompanyName", "Shippers", "ShipperID = 1") 

The criterion in this sample specifies the record in the table with a ShipperID field of one. This is an AutoNumber field with a table data type of Long Integer. When this type of criterion is acceptable for your solution, it simplifies the use of domain aggregate functions. This DLookup function returns the field value from the single record that matches the criterion. If more than one record matches the criterion for a DLookup function, DLookup returns the first matching record in the domain source.

Other domain aggregate functions, such as DSum, DCount, and DAvg, compute results based on one or more records. The following VBA statement prints the number of past due invoices to the Immediate window based on a DCount function calculation:

Debug.Print DCount("InvoiceDate", "InvoiceDates", _     "InvoiceDate < Now()-30") 

This sample computes a count of invoices that are past due and prints the result. When used on the InvoiceDates table in the sample file for Chapter 2 on 9/12/98, this DCount function returns two records. You might want to add new records to the table or change some dates to get a different count when you try this sample.

Figure 1-27 shows a form that allows the user to enter the red, green, and blue values for a color and then displays the color when the user clicks the text box on the right. The red, green, and blue values for a color can range from 0 through 255. If all three numbers are 0, the color is black. If all three numbers are 255, the color is white. Making any single number 255 while setting the other two to 0 creates an intense red, green, or blue color. The settings on my computer display the color for Figure 1-27 as a pale red or pink.

Figure 1-27. A form showing a color viewer.

The following event procedure for the text box on the right in Figure 1-27 does the simple processing for this color viewer. Aside from checking for Null entries in the color number text boxes, the procedure consists of a single statement. The statement uses the RGB function to set the BackColor of the large text box on the form's right.

 Private Sub txtColorMe_Click() 'If null color field, set a default value.     If IsNull(Me.txtRed.Value) Then         Me.txtRed.Value = 0     End If     If IsNull(Me.txtGreen.Value) Then         Me.txtGreen.Value = 0     End If     If IsNull(Me.txtBlue.Value) Then         Me.txtBlue.Value = 0     End If 'Set BackColor property.     Me.txtColorMe.BackColor = RGB(Me.txtRed.Value, _         Me.txtGreen.Value, _         Me.txtBlue.Value) End Sub 

Checking for Null values is essential since a Null value can cause the RGB function to fail. There are many ways to tackle this issue, and the code in txtColorMe_Click is only one of them. For example, making the default value 0 if a color value is not specified is arbitrary. It could easily be 255 or some quantity between 0 and 255. Another option is to not compute the RGB function but to merely send a message box that reminds the user to enter a value in all three text boxes on the left. Finally, you can actually let the run-time error occur for a Null value. Then you can trap the error and solve the problem. The next section explores ways of doing this.