Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: Calculation on Form's Before Update

    I'm trying to do something for the first time and falling flat on my face. I'd like to figure the average hours of operation per day for a business. Here's what I have:

    Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.AvgHrs = (Sum([CloseTime] - [OpenTime]) * 52 / 365) - (([DaysClsd] / 365) * (Sum([CloseTime] - [OpenTime]) * 52 / 365))

    End Sub

    AvgHrs in an ubound text box on a subform that includes the hours of operation for one week. I'm getting the error sub or function not defined.

    Any help would be welcome.

    E

  2. #2
    Join Date
    Dec 2003
    Posts
    6

    Re: Calculation on Form's Before Update

    You're getting the error because "SUM" is not a Visual Basic function. If you're trying to get a summation of all the records of a particular field, you will need to use the "DSum" function. Also, you're going to have a problem if any of the "CloseTime", "OpenTime", or "DaysClsd" are defined as Date/Time fields. You will need to use the DateDiff function to subtract dates. If these are numeric fields, you should be ok. Try the following function; I added the "nz" function to deal with any possible null fields; if any of the fields are null, you will get an error.

    Me.AvgHrs = ((nz([CloseTime]) - nz([OpenTime])) * 52 / 365) - ((nz([DaysClsd]) / 365) * ((nz([CloseTime]) - nz([OpenTime])) * 52 / 365))

Posting Permissions

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