Handling "null" return by DLookup on an integer

[michane]

My project on Leave application is almost done, however I'm really stuck at this

This coding happens after the combobox of Staff leave types (Annual, Medical,..) are selected.

Private Sub cmbLeaveType_AfterUpdate()
    Dim strSearch As String
    Dim intMax, intBal As Integer
 
    If cmbLeaveType.SelText = "Annual" Then
        'Retrieves leave record from "Leave_Details" table for particular staff - returns leave "ID".
        strSearch = DLookup("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName _
            & "' AND [Leave Type] = '" & Me.cmbLeaveType & "'")
 
        If strSearch Is Null Then
            'Returns the annual balance record from "Staff" table
            Me.txtAnnualBalance = DLookup("[Annual]", "Staff", "[Staff Name] = '" & Me.cmbStaffName & "'")
            Exit Sub
        Else
            'Gets the last leave record of the particular staff - returns leave "ID".
            intMax = DMax("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName & "'")
            'Retrieves the annual balance from last "ID"
            intBal = DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = '" & intMax & "'")
        End If
        Me.txtAnnualBalance = intBal
 
    ElseIf cmbLeaveType.SelText = "Medical" Then
    End If
End Sub

I have 2 tables here: "Leave_Details" & "Staff".
This is the setup for my tables:-
Leave_Details have:
1) ID (integer)
2) Staff Name (string)
3) Start Date
4) End Date
5) Number of Days Leave
6) Leave Type (string)
7) Annual Leave Balance (integer)
8) Medical Leave Balance (integer)
9) Remarks
10) Approval

Staff have:
1) ID (integer)
2) Staff Name (string)
3) Annual (integer)
4) Medical (integer)

If you look at the codes above, I will have an error if DLookup returns null.

Is there a way to work around this?

 

[pr2-eugin]

DlookUp tends to return Null when the criteria does not match.. You can use a Nz() function to set a value if in case the result is Null.. An Nz is a simplified If that checks for one condition, Null or Not Null.. Nz uses the following syntax..

Nz(theControl/Field/Function_To_Test , whatValueIfNull )

Using Nz for Domain Functions is highly recommended.. So in your case..

Nz(DLookUp(....), 0)

Also I have seen that you use

strSearch Is Null

Is Null is an SQL function, while the VBA equivalent is IsNull(), so you might need to change that.. Also Zero Length Strings are not technically Null, whihc means the condition might fail.. I normally use this to check for ZLS and Null variables..

If Len(strSearch & vbNullString) = 0 Then

Hope this helps..

 

[LPurvis]

As strSearch is declared as a string: (Dim strSearch As String)
Then the comparison to Null won't only possible fail - it always will.

As a string can never be Null, your other lookup
strSearch = DLookup(...
can also fail (fail as in raise an error if Null is returned).
So another Nz wrapping there is likely required, as we as altering the comparison check as mentioned by Butters above.

Also - you're using the SelText property of the combo?
Really unusual choice and open to misinterpretation. (Relies on the content of the combo being selected after list selection - and nothing changing that before reading that property value.)
The Value property alone should be perfectly acceptable to your needs.

 

[michane]

I did not manage to use the "Nz" or "Len" functions as I am still unsure how I can use it in my codes.. however I used "IsNull":

Private Sub cmbLeaveType_AfterUpdate()
    Dim intMax, intBal As Integer
 
    If cmbLeaveType.Value = "Annual" Then
        Me.txtMedicalBalance = ""
        Me.txtChildInfCareBal = ""
        If IsNull(DLookup("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName _
            & "' AND [Leave Type] = '" & Me.cmbLeaveType & "'")) Then
            intBal = DLookup("[Annual]", "Staff", "[Staff Name] = '" & Me.cmbStaffName & "'") - CInt(Me.txtDaysLeave)
        Else
            intMax = DMax("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName & "'")
            If IsNull(DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax)) Then
                intBal = DLookup("[Annual]", "Staff", "[Staff Name] = '" & Me.cmbStaffName & "'")
            Else
                If DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax) - CInt(Me.txtDaysLeave) < 0 Then
                    MsgBox "Not enough annual leave balance - You only have " & intBal & " days of annual leave remaining! - Please verify"
                    Exit Sub
                Else
                    intBal = DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax) - CInt(Me.txtDaysLeave)
                End If
            End If
        End If
        Me.txtAnnualBalance = intBal
 
    ElseIf cmbLeaveType.Value = "Medical" Then
    End If
End Sub

Now it worked, but I am having problems with my calulations of intBal.
My form has the following controls:
txtID (Leave ID)
cmbStaffName (Name of Staff)
txtStartDate (Start date of leave)
txtEndDate (End date of leave)
txtDaysLeave (Days of Leave) *Calculated from End date - Start Date
cmbLeaveType (Leave Types - Annual, Medical...)
txtAnnualBalance (Remaining annual leave available)
txtMedicalBalance (Remaining medical leave available)

When I select "Annual" in my combobox "cmbLeaveType", it will start to check the "Leave_Details" table, for the "Annual Leave Balance". If annual leave has not been recorded before (ie. no annual leave applied yet), it will look up the "Annual" (the allowed annual leave) from "Staff" table & minus one from "txtDaysLeave".

However, how come when I select the "Annual", it keep refilling with the "Annual" from "Staff" table without minus anything

 

[pr2-eugin]

michane, I thought I explained the use of Nz() pretty good.. maybe I was wrong.. I have added the hyperlink where you have a detailed explanation of how to use them.. you might want to check.. Sometimes you need to do experiment something you have never done before.. Using Nz() might be one such instance..

This is very important, because..
* Avoids the ugly Runtime Error..
* You will not be able to do any Arithmetic or Logical tests.. Even if you do, the result will be Null.. thus leading you nowhere..

Coming to your problem, I have no idea how the data is inside the table/how this code would work.. one suggestion would be to Debug the Code using Immediate window, see if you get the values right by printing them out OR Stepping through the code..

 

[michane]

I managed to use Nz now, thank you!
Found out my earlier problem & removed all those " - CInt(Me.txtDaysLeave)" at the beginning of the codes to put together with "Me.txtAnnualBalance = intBal" instead:

Private Sub cmbLeaveType_AfterUpdate()
    Dim strSearch As String
    Dim intMax, intBal As Integer
 
    If cmbLeaveType.Value = "Annual" Then
        Me.txtMedicalBalance = ""
        Me.txtChildInfCareBal = ""
        strSearch = Nz(DLookup("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName _
            & "' AND [Leave Type] = '" & Me.cmbLeaveType & "'"), "")
        If strSearch = "" Then
            intBal = DLookup("[Annual]", "Staff", "[Staff Name] = '" & Me.cmbStaffName & "'")
        Else
            intMax = DMax("[ID]", "Leave_Details", "[Staff Name] = '" & Me.cmbStaffName & "'")
            strSearch = Nz(DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax), "")
            If strSearch = "" Then
                intBal = DLookup("[Annual]", "Staff", "[Staff Name] = '" & Me.cmbStaffName & "'")
            Else
                If DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax) - CInt(Me.txtDaysLeave) < 0 Then
                    MsgBox "Not enough annual leave balance - You only have " & intBal & " days of annual leave remaining! - Please verify"
                    Exit Sub
                Else
                    intBal = DLookup("[Annual Leave Balance]", "Leave_Details", "[ID] = " & intMax)
                End If
            End If
        End If
        Me.txtAnnualBalance = intBal - CInt(Me.txtDaysLeave)
     ElseIf cmbLeaveType.Value = "Medical" Then
     End If
End Sub

But now I got trouble using DMax..
How to do it such that it will search for the LAST record of "Annual" leave type stored PREVIOUSLY? - Whereby I have a few records (of multiple Leave types "Annual, Medical.." of that same staff

 

[pr2-eugin]

Would DLast be what you are looking for?

Other than that, if you store the Annual leave by some date or if there is any possibility that you can apply a sort order then you could also give ELookUp a try.. but I think DLast should do the trick in this case..