Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: How do I convert this code to Function

    I call this Public Sub from an exit of a control. How do I convert to a function? (novice on functions)


    Public Sub CalcDwell()
    ' control to evaluate is Dwell_St_Dt
    ' Check if the control is empty
    If IsNull(Dwell_St_Dt) Or Me.Dwell_St_Dt = "" Then Exit Sub

    ' Assign values to variables
    Dim vmonths, vdays As Integer
    vmonths = DateDiff("m", Dwell_St_Dt, Date)

    vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)

    ' Assign calculated variable to txtfield
    If Dwell_St_Dt > Date Then
    txtDTim = 0 & " Mo " & 0 & " Days"
    Else
    txtDTim = vmonths & " Mo " & vdays & " Days"
    End If
    End Sub
    We tend to look at Linear paths which can lead us to a path of resistance!

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Quote Originally Posted by PJHAction View Post
    I call this Public Sub from an exit of a control. How do I convert to a function? (novice on functions)


    Public Sub CalcDwell()
    ' control to evaluate is Dwell_St_Dt
    ' Check if the control is empty
    If IsNull(Dwell_St_Dt) Or Me.Dwell_St_Dt = "" Then Exit Sub

    ' Assign values to variables
    Dim vmonths, vdays As Integer
    vmonths = DateDiff("m", Dwell_St_Dt, Date)

    vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)

    ' Assign calculated variable to txtfield
    If Dwell_St_Dt > Date Then
    txtDTim = 0 & " Mo " & 0 & " Days"
    Else
    txtDTim = vmonths & " Mo " & vdays & " Days"
    End If
    End Sub
    Code:
    Public Function Calc_Dwell(ByVal DwellStDt As Date) As String
    ' control to evaluate is Dwell_St_Dt
    ' Check if the control is empty
    
    If IsNull(DwellStDt) Or DwellStDt = "" Then Exit Function
    
    ' Assign values to variables
    Dim vmonths, vdays As Integer
    vmonths = DateDiff("m", DwellStDt, Date)
    
    vdays = DateDiff("d", DateAdd("m", vmonths, DwellStDt), Date)
    
    ' Assign calculated variable to txtfield
    If DwellStDt > Date Then
    txtDTim = 0 & " Mo " & 0 & " Days"
    Else
    txtDTim = vmonths & " Mo " & vdays & " Days"
    End If
    
    Calc_Dwell = txtDTim
    
    End Function
    Copy and paste the Calc_Dwell() Function code into a Standard VBA Module and save the module.

    Syntax for calling the Function from a Form Control:

    Code:
    =Calc_Dwell(Me!Dwell_St_Dt)
    From the Form VBA Module:
    Code:
    X = Calc_Dwell(Me!Dwell_St_Dt)
    Last edited by apr pillai; 07-22-15 at 13:59. Reason: to add calling methods
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    strictly speaking in VB/VBA a function returns a value, a SUB routine doesn't, but its fairly arbitrary.
    you don't say where you are suing the function. In an ideal world a function should have all arguments passed into it, and return a value (that returned value could be a single value or an array

    Code:
    public function myfunction(Dwell_St_Dt as datatypeB*) as datatypeA*
      ' control to evaluate is Dwell_St_Dt
      ' Check if the control is empty
      If Not IsNull(Dwell_St_Dt) and Me.Dwell_St_Dt != "" Then
        ' Assign values to variables
      Dim vmonths, vdays As Integer  'what datatype should vmonths be? prob shoudl be declared on a separate line
      vmonths = DateDiff("m", Dwell_St_Dt, Date)
      vdays = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)
    
        ' Assign calculated variable to txtfield //instead of assigning the value directly to a text field, return the value
        'purists would argue the variable to be returned should have a more descriptive name and that value be exprressly RETURNed
        'its one of the quirks of VB/VBA that you can assign a value to a variable of the same name as the function and it will be RETURNed without expressly doing so
        If Dwell_St_Dt > Date Then
          myfunction = 0 & " Mo " & 0 & " Days"
        Else
          myfunction = vmonths & " Mo " & vdays & " Days"
        End If
      else
        myfunction = null
      endif
      return myfunction 'no need for this if your working variable is called the same as the function itself in VB/VBA. however I'd argue its good practice to explicitly return a value form a function
    end function
    datatypeA, datatypeB. in an ideal world you shoudl alwasy expressly state what datatype each parameter and the retuyrn shoudl be. VB/VBA will default to variant if not expressly declared. but even if the datattype shoudl be variant I'd strongly recommend that your declare it as such

    the basic goal for a function is that it should only work on values expressly passed to it as part of the call
    AND should only return values likewise.
    So try to avoid reading or setting control properties or variables that are not within the function EVEN if the function is only being used within a single form/report

    arguably you should separate the formatting from the value
    Code:
    public function myfunction(Dwell_St_Dt as Date) as variant
      dim ReturnValue as integer[5] 'not sure this will work as Ive not tested it ;)
      'setup some default return values
      ReturnValue[0] = 0 'equates to years
      ReturnValue[1] = 0 'equates to months
      ReturnValue[2] = 0 'equates to days
      ReturnValue[3] = 0 'equates to hours
      ReturnValue[4] = 0 'equates to minutes
      ReturnValue[5] = 0 'equates to seconds
      If Not IsNull(Dwell_St_Dt) and Me.Dwell_St_Dt != "" Then
        ' Assign values to variables
        ReturnValue[1] = DateDiff("m", Dwell_St_Dt, Date)
        ReturnValue[2] = DateDiff("d", DateAdd("m", vmonths, Dwell_St_Dt), Date)
      endif
      return ReturnValue
    end function
    //add ano9ther function to handle the formatting
    //give the fucntion a meanign full name instread of MyFucntion ;)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    Thanks for the response. Back at it.

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •