Results 1 to 5 of 5

Thread: #Error (Any)

  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    #Error (Any)

    If I am using a VBA function as the datasource for a field on a form, and one of its arguments is a field on the form, when you go to a new row on the form you obvioully have #Error in the field with the function. Is there any way to put some error code in the function to get around this?
    Thanks
    Zave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Error (Any)

    In the first place, you shouldn't declare the argument as a specific data type such as String, since a String (or Long etc.) can't be Null. Declare the argument as a Variant instead.
    You can then test whether the argument is Null:

    If IsNull(myargument) Then
    ...
    Else
    ...
    End If

    or use the Nz function to replace Null with another value, for example Nz(myargument, 0) or Nz(myargument, "")

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #Error (Any)

    I had tried that but it still did not work
    Any ideas . I had changed both arguements to variant and tested for null on one of them as you said, but it still did not work

    Here is the code:

    Function fAttendanceClass(intId As Integer, intClass As Integer) As String

    Dim intPresent As Integer
    Dim intAbsent As Integer
    Dim intLate As Integer
    Dim intexcused As Integer
    Dim intTotal As Integer

    intPresent = DCount("[Present]", "tblAttendance", "present = 'p' and id = " & intId & " and [Class ID]= " & intClass)
    intAbsent = DCount("[Present]", "tblAttendance", "present = 'a' and id = " & intId & " and [Class ID]= " & intClass)
    intLate = DCount("[Present]", "tblAttendance", "present = 'l' and id = " & intId & " and [Class ID]= " & intClass)
    intexcused = DCount("[Present]", "tblAttendance", "present = 'e' and id = " & intId & " and [Class ID]= " & intClass)
    intTotal = intPresent + intAbsent + intLate + intexcused
    If intAbsent = 0 Then
    fAttendanceClass = "100%" & " Late= " & intLate & " excused= " & intexcused
    Else
    fAttendanceClass = Nz(FormatPercent(intAbsent / intTotal) & " Late= " & intLate & " excused= " & intexcused)
    End If
    End Function

    and here is the way it is used in a text box:

    =fAttendanceClass([Forms]![frmStudents]![StudentID],[ClassName])

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Error (Any)

    Your code doesn't declare the arguments as Variants and doesn't test for Null. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Function fAttendanceClass(intId As Variant, intClass As Variant) As String
    Dim intPresent As Integer
    Dim intAbsent As Integer
    Dim intLate As Integer
    Dim intexcused As Integer
    Dim intTotal As Integer

    If IsNull(intId) Or IsNull(intClass) Then
    Exit Function
    End If
    ...

    There's no point in using Nz in

    fAttendanceClass = Nz(FormatPercent(intAbsent / intTotal) & " Late= " & intLate & " excused= " & intexcused)

    for the expression will never be null.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: #Error (Any)

    I see my mistake. I used End in the IF ISNULL rather than exit function
    Thanks as usual
    It is always a learning experience

Posting Permissions

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