Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: adding functions from VBA to reports

    I have a report that is based on a cross tab qry. If one of the columns has no data the rpt shows #Error.

    I would like to create a function that uses "Select Sum(Ray) from ExpTypes" to run off ado, if this value returns #Error (from error handling ) return 0


    Any help??

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT nz(SUM(Ray), 0)

    ??
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2003
    Posts
    311
    I dont get it? what nz?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    nz() is a function that evaluates for null. It is of the following syntax:

    nz(TargetToEvaluate, ValueIfNull)

    Therefore SELECT nz(SUM(ray), 0) is stating "Select the sum of the field called ray, if that value is null, return 0"

    It may actually be better to write this as:

    SELECT SUM(nz(ray, 0))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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