[dba-SQLServer]Replacement for DLookup with 2 parameters

David Emerson davide at dalyn.co.nz
Mon Mar 3 22:14:29 CST 2003


What I am looking for here is some confirmation I am doing the right thing.

In A97 I had a simple statement to return a single value if the 
corresponding record existed (this was in the Control Source of a field -

=IIf(Not 
IsNull([CustIDNo]),nz(DLookUp("Current","tblCustStatement","CustIDNo = " & 
[CustIDNo] & " and StatementNumber = " & [InvNumber]-1),0))

Now in my ADP I am using the following (and setting the field control 
source to '=cbfAmtDue()') -

Public Function cbfAmtDue() As Currency

     Dim cnn As ADODB.Connection, rst As ADODB.Recordset

     Set cnn = CurrentProject.Connection
     Set rst = New ADODB.Recordset
     rst.Open "tblCustStatement", cnn, adOpenDynamic, adLockOptimistic

     cbfAmtDue = 0
     If Not IsNull([CustIDNo]) Then
         rst.Filter = "CustIDNo = " & Me!CustIDNo & " and StatementNumber = 
" & DLookup("InvNumber", "tblCustomers", "CustomerID = " & Me!txtCustID) - 1
         If rst.EOF <> True Then 'Statement record exists
             cbfAmtDue = rst!CurrentMth
         End If
     End If

     rst.Close
     Set rst = Nothing
     cnn.Close
     Set cnn = Nothing

It seems a long way about it but is there any simpler way?


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 




More information about the dba-SQLServer mailing list