[dba-SQLServer]Replacement for DLookup with 2 parameters

Think sprocs, David....
So your Function would be more along the lines of..

Public Function cbfAmtDue() as Currency
    Dim rst ADODB.Recordset
    Set rst = New ADODB.Recordset

    Currentproject.Connection.stp_AmountDue CustIDNo, InvNumber - 1, rst

    if rst.eof = true then
       cbfAmtDue = Null 'Or 0
    else
        cbfAmtDue = rst!CurrentMth
    end if
'--------END
Now in a sproc on your SQL Server you'd add the following sproc...

----------
Create Procedure stp_AmountDue (@CustIDno as int, @InvNumber as Int) AS
SELECT CurrentMth From tblCustStatement Where CustNoId = @CustNoId AND
InvNumber = @InvNumber
---------END

this is all OTOMH, so please check your syntax...
-Francisco
http://rcm.netfirms.com/
----- Original Message -----
From: "David Emerson" <davide at dalyn.co.nz>
To: <dba-SQLServer at databaseadvisors.com>
Sent: Monday, March 03, 2003 8:14 PM
Subject: [dba-SQLServer]Replacement for DLookup with 2 parameters


: 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
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
: