The Locals Window in the above screen shot shows the Recordset object variable rs, and specifically the value for the first field from rs, “CustRev”.
With a breakpoint set the code execution automatically stops at the marked line and enables us to view the environment at that point of execution. I tested the function by executing it from the Immediate Window in the VBA editor: ?LookupAWCustomerRevenue(1) I placed a breakpoint on the line of VBA code containing the If statement and opened the Locals Window so that I could see all the variable values at that point of execution. The NULL value scenario can be seen in the following screen shot. If Not IsNumeric(rs.Fields("CustRev").Value) Then If this happens then the funvtion will return a value of 0 instead. If a customerID passed in to the function is valid, but they have plaved no orders the SUM(TotalDue) expression will return a NULL value. The second If statement tests for a non numeric value being returned by the SELECT statement. If no value is passed in to the function a value of 0 is returned as the revenue value. Integer variables default to a value of zero if not initialised (in other words if no value is provided when the function is called). The If statement at the start checks for an intID value of 0. The SELECT statement is constructed from a literal string and the value for the variable intID that is passed into the function when it is called.
& "Initial Catalog=AdventureWorks2014 Integrated Security=SSPI " StrConnString = "Provider=SQLOLEDB Data Source=W10NBMJD\SQL2014 " _ Public Function LookupAWCustomerRevenue(intID As Long) As Currency You will, however, need to add the ADODB library to the project first. The following VBA Function can be used from within an Access VBA Project. We will look at an example that enables us to lookup a customer’s total revenue value from an Access Query.