Dlookup gone?

• Cleech

  Mr or Mrs. 500

  Points: 513

  More actions

  August 13, 2004 at 7:53 am

  #66096

  Hello All:

  I'm working on a converted access .adp and I just found out that domain aggregate functions, in particular Dlookup, are not available.  Does anyone have a good approach to resolve this?  Is there another function I could use or should I write a new function to replace it?

  Any help is greatly appreciated.

  Thanks, Cleech

• EdH334

  SSCrazy

  Points: 2857

  More actions

  August 16, 2004 at 2:08 am

  #519042

  Hi Cleech,

  Try using a sub-query instead.  For Example, in Access a query could look like:

  SELECT OrderId

  FROM Orders

  WHERE EmployeeID = DLookUp("[EmployeeID]", "Employees", "[LastName] = 'Smith'" );

  Can be represented in SQL Server as:

  SELECT OrderId

  FROM Orders

  WHERE EmployeeID = (SELECT [EmployeeID

  ] FROM Employees WHERE [LastName] = 'Smith' ) 

   

  You can also use sub-queries for variables, so in Access VBA:

   

  Dim strEmployeeName As String

   

  strEmployeeName = DLookUp("[LastName]", "Employees", "[EmployeeID] = 1234" ) 

  Can be represented in T-SQL as:

  DECLARE @CEmployeeName nvarchar(128)

   

  SET @CEmployeeName = (SELECT [LastName] FROM Employees WHERE [EmployeeID] = 1234 ) 

  Hope this helps!

• Dølle Døck

  Mr or Mrs. 500

  Points: 577

  More actions

  August 16, 2004 at 2:35 am

  #519048

  Create your own Dlookup function.

  Something like this:

  Public Function fDlookup(sField As String, sTable As String, sCriteria As String) As Variant

  On Error GoTo fDlookup_Error

      Dim rst As New ADODB.Recordset

      Dim sSQL As String

     

      fDlookup = ""

     

      sSQL = "SELECT " & sField & " " & _

              "FROM " & sTable & " " & _

              "WHERE " & sCriteria

                 

      rst.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

     

      If Not rst.BOF Then

          fDlookup = Trim$(rst(sField))

      End If

     

  fDlookup_Exit:

      If rst.State = adStateOpen Then rst.Close

      Set rst = Nothing

      Exit Function

  fDlookup_Error:

      MsgBox Err.Description, vbInformation, "Function fDlookup"

      fDlookup = Null

      Resume fDlookup_Exit

     

  End Function

   

  ---

  Regards,

  Anders Dæmroen
  epsilon.no