[dba-SQLServer]Replacement for DLookup with 2 parameters

Actually he doesn't need to use a recordset at all.  He could create a sproc
that returns "CurrentMth" as an Output Parameter.  If there is no matching
record, then the return value is NULL and he can use NZ function to convert
it to 0.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Tuesday, March 04, 2003 02:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com