1. Pull fields from an external database
2. Create Custom fields that do not exist in The Raiser's Edge
3. Calculate values in VBA that can be used in other applications when exporting from Query
To use a User Field, you must select VBA User Field on the output tab. You will also need to include any fields that you will use in your program. A Query macro can perform calculations using only the fields that are present in the output.
'Place this code in the System_Macros section
Public Sub NumberofYears(oRow As IBBQueryRow)
Dim lYears As Long
If oRow.BOF Then
lYears = 0
ElseIf oRow.EOF Then
lYears = 0
lYears = DateDiff("yyyy", CDate(oRow.Field("Date Added")), Now)
'Number of Years is the name of the VBA User Field
oRow.Field("Number of Years") = lYears
With VBA User Field in the output, right click on VBA User Field and Select Column Heading, change the value to Number of Years.
In the query that will include the Macro, select Tools, Query Options. On the Record Processing Tab, select the VBA Macro created above.
Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Microsoft Visual Basic and the tools used to create and debug procedures. Our Customer Support can help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality. If you are interested in learning more about the VBA and API modules, contact your account manager.