Results 1 to 6 of 6
  1. #1
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116

    public function not responding as expected to null values

    good morning,



    I have a function that calculates the difference between two dates. the output is a string in the form "67 years, 229 days"

    this obviously requires two dates as arguments (DoB, and DoR), and it is not possible for the second argument DoR to be anything other than a date. if DoR is not known there is no record in the database.

    the first argument DoB can be a null. so my function checks for Nulls, or at least it is supposed to but I can't get it to work properly.

    I have checked using the vartype() function and where DoB is not known vartype() returns 1, indicating a null. so this is not zls type scenario.

    however, where both dates are known, the return is correct, but when DoB is not known the function returns #Error.

    the function looks like this:

    Code:
    Public Function Age_m(DoB As Date, DoR As Date) As String
    
    ' this returns the athlete's age as a text string, nn years, nnn days '
    Dim race As Date
    Dim delta As Integer
    Dim days As Integer
    If IsNull(DoB) Then
    Age_m = " - "
    Else
    race = DateSerial(Year(DoR), Month(DoB), Day(DoB))
    delta = IIf(DoR - race > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1)
    days = IIf(DoR - race > 0, DoR - race, 365 + DoR - race)
    Age_m = Format(delta, "00") + " years, " + Format(days, "0") + " days"
    End If
    End Function

    any clues as to how to improve this would be greatly appreciated.

    thank you

    Cottonshirt

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try IsDate()

    However, I see little reason to call a function, if it cannot work without what it needs?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The Date data type can't be Null. You'd have to use Variant:

    DoB As Variant
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    so this is not zls type scenario.
    Yet that would be the most logical cause. Try

    If Len(Nz(DoB,"")) & vbNullString = 0 Then
    or (something I've never tried)
    If Len(Nz(DoB,vbNullstring)) = 0 Then

    If either of those solve it, then I'd say you have a zls somewhere in the DoB values being processed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Quote Originally Posted by pbaldy View Post
    The Date data type can't be Null. You'd have to use Variant:

    DoB As Variant

    we have a winner.

    thank you very much indeed.


    Cottonshirt

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 10-17-2018, 09:26 PM
  2. Null value not responding in VBA Code
    By George in forum Access
    Replies: 4
    Last Post: 02-08-2018, 07:43 PM
  3. public function values from form
    By ShostyFan in forum Programming
    Replies: 4
    Last Post: 10-02-2017, 02:11 PM
  4. Replies: 10
    Last Post: 05-01-2017, 06:11 AM
  5. Replies: 1
    Last Post: 08-03-2014, 07:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums