Our development standards

	Professional Database Developers
Aldex Software Ltd.

Introduction

The following code is all copyright Aldex Software Ltd. unless otherwise indicated. You may use this code freely for your own personal use. You may not include any part of this code in any commercial software, code libraries or listings without written permission.

All of this code is provided 'as is', you use it at your own risk! If you'd like to contribute any of your own code please do so (your copyright will of course be acknowledged). In the following examples we have tried to comment the code sufficiently for you to follow but have omitted error processing for reasons of space and clarity. An adapted version of the Leszynski/Reddick naming convention has been used in these examples.

A String Replace function

Public Function StrReplace(pstrIn, pstrOld, pstrNew)
 ' Copyright ©1997 Aldex Software
 ' This function takes an input string (pstrIn) and replaces any
 ' occurrences within it (case insensitive) of pstrOld with pstrNew.
 ' To make it case sensitive change the vbTextCompare argument
 ' Note the use of the NZ function makes it 97 specific, for
 ' earlier versions replace this with an IsNull test.
 ' Useage:
 '  MyNewSting = StrReplace("Hello Henry. How is Harry and Henrietta", "He", "X")
 '  Results in MyNewStr = "Xllo Xnry. How is Harry and Xnrietta"

Dim strWork as String
Dim strL As String
Dim strR As String
Dim intX As Integer

  strWork = pstrIn
  
  Do
    intX = Nz((InStr(1, strWork, pstrOld, vbTextCompare)), 0)
    If intX > 0 Then
       strL = Left$( strWork, intX - 1)
       strR = Right$( strWork, (Len(strWork) - intX - Len(pstrOld) + 1))
       strWork = strL & pstrNew & strR
    Else
      Exit Do 'Escape from the loop if nothing else to do
    End If
  Loop

  StrReplace = strWork
End Function

Calculates the Average of a (variable) set of numbers

Public Function aslib_Ave(ParamArray pvarArray()) as Double
' Copyright ©1997 Aldex Software 
' Gives the mean (=average) of a set of numeric values.
' We are using a ParamArray here so that the number of
' arguments passed to the function is variable.
' Environment: Access 97
' Author:  D.P.Saville, Aldex Software, July 1997.
' Contact: dps@aldex.co.uk, http://www.aldex.co.uk/
' Useage:  aslib_ave(3,5,9,2,56,7,43,3) will return 16.
    
Dim varX As Variant
Dim dblAccum As Double
Dim intN As Integer
    
    For Each varX In pvarArray
        If Not IsNull(varX) Then
            dblAccum = dblAccum + varX
            intN = intN + 1
        End If
    Next
    
    If intN = 0 Then
        aslib_Ave = 0
    Else
        aslib_Ave = dblAccum / intN
    End If
End Function

Which Financial Year is a date in?

If you deal with financial data you will probably need to calculate which tax or financial year a particular date falls into. The following function will return the Financial Year for any given input date. The start of the Fiancial Year is defined by the second and third parameters (which contain month and day) of the first active line of the function. These are currently set to 4, 1 (= April 1st) but can be changed for any start date, including ones where the financial year does NOT start at the beginning of the month. Thus you can cope with finacial years which start, say, on Feb 26. If you want the output in the format 'FY98' then either amend the function internally or just alter the call to "FY" & Right$(as_FinancialYear(DateToTest),2). As with all date processing routines, remember to check thoroughly with all possible date inputs before use.

Public Function as_FinYear(pdat As Date) As Integer

  ' Copyright ©1997 Aldex Software	
  ' Author D.P.Saville, Aldex Software, www.aldex.co.uk
  ' USE:
  ' TheYear = as_FinYear(DateToTest)
  '   EG  as_FinYear(#31/Mar/1997#) gives 1996
  '   and as_FinYear(#1/Apr/1997#) gives 1997
 
    If pdat >= DateSerial(Year(pdat), 4, 1) Then
        as_FinYear = Year(pdat)
    Else
        as_FinYear = Year(pdat) - 1
    End If

End Function

And Finally....

If you have any comments or suggestions, or if you would like to contribute to this page then please contact us.

Return to front page