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

    Error checking for Null value in IF EndIf statement

    noob alert,



    I have just written my first function in VBA and it kind of works but the error checking part doesn't seem to do anything and I would appreciate some guidance with that, thank you.

    the variable DoB is date of birth and DoR is the date of a race. the function calculates how old the athlete was on the day of the race and returns a number as decimal years.

    the main part of the function works and calculates the correct value. the first part of the If statement is supposed to check if there is actually a date of birth, and if DoB is null, set the function value to zero. however, when I run this records with no date of birth return an #Error.

    I could obviously get round this by setting Is Not Null as a criteria on the query but I would also like to understand why the VBA If statement is not working.

    the function I have is as follows:

    Public Function Ageatmedal(DoB As Date, DoR As Date)
    If IsNull(DoB) Then
    Ageatmedal = Format(0, "##.0000")
    Else
    ran = DateSerial(Year(DoR), Month(DoB), Day(DoB))
    delta = IIf(DoR - ran > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1)
    days = IIf(DoR - ran > 0, DoR - ran, 365 + (DoR - ran))
    Ageatmedal = Format((days / 365) + delta, "##.0000")
    End If
    End Function

    very grateful for any assistance,

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the error checking part
    This is validation. You have no error checking (correctly referred to as error trapping).
    the first part of the If statement
    You mean the IIF part? I don't see anything wrong with your If block.
    As for the expression, dateserial returns a date, but since you haven't declared ran as anything, I suspect you need to wrap the variable in date delimiters (#). I also suspect you don't use (or at least haven't in this procedure) Option Explicit since you don't seem to declare ran, days or delta. In that case, you're just asking for trouble.

    Wondering if you couldn't make this simpler using DateDiff function.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Cottonshirt is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2018
    Posts
    116
    This is validation. You have no error checking
    I'm sorry, but I don't understand the distinction you are making. there either is a date of birth, or there isn't.

    in those cases where there is no date of birth the function, as written, returns an #Error. therefore, the first clause:

    If IsNull(DoB) Then
    Ageatmedal = Format(0, "##.0000")

    is not performing as expected.

    the second clause of the If statement, starting with ran = DateSerial... worked on its own as a standalone function before I put it into the IF statement.

    I am, however, grateful for alerting me to Option Explicit, which I had not heard of previously. I have now implemented this. the function now reads:

    Public Function Ageatmedal(DoB As Date, DoR As Date)
    Dim ran As Date
    Dim delta As Integer
    Dim days As Integer
    If IsNull(DoB) Then
    Ageatmedal = Format(0, "##.0000")
    Else
    ran = DateSerial(Year(DoR), Month(DoB), Day(DoB))
    delta = IIf(DoR - ran > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1)
    days = IIf(DoR - ran > 0, DoR - ran, 365 + (DoR - ran))
    Ageatmedal = Format((days / 365) + delta, "##.0000")
    End If
    End Function

    but it still returns an #Error when there is no Date of Birth.

    your suggestion that I might use the DateDiff() function is interesting. I tried that before attempting to create my own function, but it returned the wrong answer in approximately 40% of cases.

    thank you.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    DoB and DoR are explicitly declared as Date type.

    Only Variant variables can hold Null. Therefore your function returns #Error when field has no date value and try to pass Null to the function.

    Your calculation will likely be off a little because not every year has 365 days.

    Maybe instead of custom function this will satisfy: IIf(IsNull(DOB), 0, Round(DateDiff("m", DOB, DOR) / 12)) or IIf(IsNull(DOB), 0, Int(DateDiff("m", DOB, DOR) / 12))

    A variable declared as Date has a default value of 12:00:00 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    DateDiff function should work.
    Copy below to a module.
    Test in immediate window:
    ?fcnAgeAtMedal("11/1/2000",Date())
    or
    ?fcnAgeAtMedal("August 12, 1992","1/1/2000")
    or
    ?fcnAgeAtMedal(Forms!frmTest!DOB,Forms!frmTest!DOR )

    Code:
    Public Function fcnAgeAtMedal(DoB as string, DoR as string)
        If Not IsDate(DoB) Then
            MsgBox "DOB invalid format"
            Exit Function
        End If
        If Not IsDate(DoR) Then
            MsgBox "DOR invalid format"
            Exit Function
        End If
        fcnAgeAtMedal = DateDiff("yyyy", DoB, DoR)
    End Function

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Explicitly declared variable not declared Variant cannot hold Null. Issue is the function errors because trying to pass Null in the argument. Declaring variables as String will have the same issue as Date type. See post 4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with June7 for the current issue. There could be others, but can't tell for sure. To elaborate,
    If IsNull(DoB) Then
    Ageatmedal = Format(0, "##.0000") will make Ageatmedal 0 if DoB is null. You can't later ignore the fact that DoB is Null and use it as

    IIf(DoR - ran > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1)
    This becomes Year(Null)

    Re the other comments, #Error has nothing to do with error trapping/handling. #Error is a result you can get in a control when a control cannot resolve its expression. Another possibility is #Name.
    This If IsNull(DoB) Then is validation. If something is Null (or =, or < or > etc.) then do the following...(whatever that may be)

    Error trapping is
    after the declarations (Dim statements)
    On Error GoTo errHandler (my default code block to go to when there is an application error).
    more code
    more code

    exitHere:
    close stuff, etc
    exit sub or function

    errHandler:
    present message box with error information. Maybe handle different errors different ways with select case block or If block.
    Resume exitHere (or resume next, or resume after handling, as appropriate)

    End Sub or function
    Suggest you read up on error handling/trapping as it will make your code and user interaction better.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Review http://allenbrowne.com/ser-23a.html

    Don't implement until ready to publish db to users. Error handling can interfere with debugging. Often have to disable so the debugger will break on line causing error instead of providing a cryptic message that doesn't specify where the error is.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Cottonshirt is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Micron,

    thank you for clarifying the difference between error checking and validation. that will become useful when I need to create code to check that data entry has been completed within normal parameters and so forth. thank you.

    You can't later ignore the fact that DoB is Null and use it as IIf(DoR - ran > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1) This becomes Year(Null)
    however, this appears to be incorrect. my trivially naive understanding of how an IF Endif statement works is as follows:

    IF (this is true)
    do this
    ELSE
    in other cases, do this
    ENDIF

    and since a value cannot simultaneously be Null and Not Null I do not, "later ignore the fact that DoB is Null," since the second clause only implements IF DoB is Not Null. if DoB is null program execution exits the IF ENDIF when it gets to ELSE. maybe I'm wrong, but that's how I think it works.

    Davegri

    I spent a long time understanding your code from which I learned about MsgBox, thank you, but your claim that "DateDiff function should work" is not confirmed by tests. It gave the wrong answer in 38% of cases. the reason it doesn't apply here is because the function returns the number of time intervals between two dates where the time intervals may only be those drawn from a set list (yyyy, q, m, d, etc) and none of these are useful in this context. suppose I used days as the time interval. I then have to either enter some immensely complicated date maths to calculate how many years and days it is, or else I make some big assumptions about months being, on average, 31 days and years being, on average, 364.25 days, and making all of these assumptions is losing accuracy. I was, ultimately, looking for an answer in the form "23 years, 112 days" and DateDiff can't do that with sufficient accuracy to make it worth doing. thank you for replying, though, I learned something useful from your post.

    June7
    thank you for supplying the solution and I'm sorry I didn't get it the first time you said it.

    you said that the only variable type that can take a Null is Variant. in fact you said it twice.

    at first, I tried declaring a new Variant, then passing DoB to the Variant, test the Variant to see if it's null, and proceed. but I still got an #Error for some reason. after going for a run I had the daft idea to make DoB a variant and that worked.

    all I did was change the first line from: Public Function Ageatmedal(DoB As Date, DoR As Date)

    to: Public Function Ageatmedal(DoB As Variant, DoR As Date)

    and that worked.

    I'm not real clear on why: ran = DateSerial(Year(DoR), Month(DoB), Day(DoB)) works when DoB is a Variant rather than a date, but the underlying value is just a number anyway and 12:34.45 and 23-Jan-1976 are just formatting decoration so maybe what it "is" on top doesn't matter as long as the underlying mathematics is correct.

    many thanks for everyone's help and consideration. learned a lot from this.

    cheers.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are correct about how If Then Else works.
    Public Function Ageatmedal(DoB As Date, DoR As Date) could be Nullaccording to your posts. I was trying to elaborate on what June7 was saying. I see now that it came across as though I meant the expression was still being evaluated by your code. I should have left out that part and just gone with "#Error is a result you can get in a control when a control cannot resolve its expression" that I wrote, and left it at that.
    Sorry.

    P.S - I've altered the VIP moniker that the forum seems to give someone who has posted a lot so as to be something more appropriate.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I offered you a solution that does not use a custom function and don't think it is terribly complicated.

    The custom function works when DoR and DoB are declared as Variant because the function itself has not explicitly been declared as a particular data type, therefore it can return Null. When not explicitly declared a variable or function will default to Variant type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Error checking
    By Me.Howard in forum Access
    Replies: 6
    Last Post: 11-12-2015, 05:36 AM
  2. Error checking
    By Me.Howard in forum Forms
    Replies: 5
    Last Post: 10-15-2015, 04:06 PM
  3. Error Checking in a text box
    By Paul H in forum Forms
    Replies: 2
    Last Post: 07-23-2015, 01:02 PM
  4. Error Checking within Form
    By phd4212 in forum Forms
    Replies: 1
    Last Post: 05-09-2012, 02:46 PM
  5. Problem checking for Null Value of a Textbox.
    By SIGMA248 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 08:56 AM

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