I need an ELookUp Example

[BLFOSTER]

Can someone please share an example of the ELookUp function with the 4th argument included (OrderBy)?
Here is my ELookUp which is the control source of a text box on a form:

=ELookUp("CountOfActivityID","3Wks0AttendanceMainQ ","ActivityID = " & [Forms]![Form21]![form3new].[Form]![ActivityTableID])

The function is returning values from a query that indicates how many weeks in a row the student has had 0 hours of attendance - up to 3 weeks. I want to be able to sort it descending to bring all the 3's to the top.....

This is from Allen Browne but for some reason, I am not understanding ........

Thank you
Becky

[June7]

=ELookUp("CountOfActivityID","3Wks0AttendanceMainQ ","ActivityID = " & [Forms]![Form21]![form3new].[Form]![ActivityTableID], "Somefield {optional DESC}")

or maybe specific to your requirement.

=ELookUp("CountOfActivityID","3Wks0AttendanceMainQ ","ActivityID = " & [Forms]![Form21]![form3new].[Form]![ActivityTableID], "CountOfActivityID DESC")

[BLFOSTER]

Gosh...thanks so much! Now I just get that pesky "too few parameters expected 1" message. The function returns the correct values without the OrderBy clause......I wanted to put this function on a command button if I can get it to work. So when the user clicks the button the form sorts by weeks of no attendance. Do you know why this won't work?
Me.NoAtt = ELookup("CountOfActivityID", "3Wks0AttendanceMainQ", "ActivityID = " & [Forms]![Form2]![Form3New].[Form]![ActivityTableID], "NoAtt DESC")

[June7]

No idea. Never used this function.

If the sorting is not needed then why provide the argument parameter?

Step debug. Refer to link at bottom of my post for debugging guidelines.

I would have to examine your db and debug.

[BLFOSTER]

Maybe this will help.....I don't know. About to give up

When I debug:
Private Sub Command866_Click()
Me.NoAtt = ELookup("CountOfActivityID", "3Wks0AttendanceMainQ", "ActivityID = " & [Forms]![Form2]![Form3New].[Form]![ActivityTableID], "NoAtt DESC")
End Sub

Resume Exit_Elookup is highlighted

Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional OrderClause As Variant) As Variant
On Error GoTo Err_ELookup
    'Purpose:   Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. allen@allenbrowne.com
    'Updated:   December 2006, to handle multi-value fields (Access 2007 and later.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim db As DAO.Database          'This database.
    Dim rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSql As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.
    'Initialize to null.
    varResult = Null
    'Build the SQL string.
    strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSql = strSql & " WHERE " & Criteria
    End If
    If Not IsMissing(OrderClause) Then
        strSql = strSql & " ORDER BY " & OrderClause
    End If
    strSql = strSql & ";"
    'Lookup the value.
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
    If rs.recordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(rs(0)) = vbObject Then
            Set rsMVF = rs(0).Value
            Do While Not rsMVF.EOF
                If rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!FileName & strcSep
                Else
                    strOut = strOut & rsMVF![Value].Value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = Left(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = rs(0)
        End If
    End If
    rs.Close
    'Assign the return value.
    ELookup = varResult
Exit_ELookup:
    Set rs = Nothing
    Set db = Nothing
    Exit Function
Err_ELookup:
    MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
End Function

[June7]

Doesn't help. Need to disable the On Error GoTo line (comment the line with an apostrophe). This will allow the debugger to stop on the line that is actually causing the error.

On Error GoTo is only useful for db deployment so that users have an 'escape' mechanism if the code fails on them.

Post lengthy code within CODE tags to retain indentation and be easier to read.

[BLFOSTER]

I didn't even know about the code tags! Thank you!

Without the 'on error GoTo Err_ELookup.

'Lookup the value.

 Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)    

[June7]

I am guessing there is something about the query (3Wks0AttendanceMainQ) given as the domain. Post that query SQL statement.

[BLFOSTER]

Here is 3Weeks0AttendanceMainQ:

SELECT Activities1.[Activity - TRACS Code], CQueryMain.ActivityID, CQueryMain.CountOfActivityID, CQueryMain.[Total Hours]
FROM CQueryMain LEFT JOIN (ClientTable1 RIGHT JOIN Activities1 ON ClientTable1.[Student ID] = Activities1.[Client ID (ref)]) ON CQueryMain.ActivityID = Activities1.ActivityTableID
WHERE (((Activities1.[Activity - TRACS Code])="SW" Or (Activities1.[Activity - TRACS Code])="JO" Or (Activities1.[Activity - TRACS Code])="WE" Or (Activities1.[Activity - TRACS Code])="HS" Or (Activities1.[Activity - TRACS Code])="LS") AND ((CQueryMain.[Total Hours])=0) AND ((Activities1.[Activity/Hire - Start]) Is Not Null) AND ((Activities1.[Activity/Hire - End]) Is Null))
ORDER BY CQueryMain.CountOfActivityID DESC;

Here is CQueryMain:

SELECT Hours.ActivityID, Count(Hours.ActivityID) AS CountOfActivityID, Hours.[Total Hours]
FROM Hours
WHERE (((Hours.[Week Of]) Between Date()-(24+Weekday(Date(),2)) And Date()-Weekday(Date(),1)))
GROUP BY Hours.ActivityID, Hours.[Total Hours]
HAVING (((Hours.[Total Hours])=0));

[June7]

Well, not seeing any problems there. Neither query uses dynamic input parameters so no idea why the Recordset is expecting another parameter.

Afraid that's all I can contribute without touching your db.

[BLFOSTER]

It will take me a while to make a demo copy.....really large and lots of confidential info. Is that what you mean?

[June7]

Yes, hopefully that will allow me to replicate the issue and debug.

I just tested the function by calling it from the VBA immediate window. No error.

Why do you feel need to use ELookup? I never have. DLookup has always served my purposes.

[BLFOSTER]

Oh, I was looking for a way to sort on the field and everything I read pointed to ELookup. If it isn't necessary, I would be ecstatic! I tried every way I could think of to sort on that field and couldn't get it to work. I did have DLookup, which worked fine but I couldn't get it to sort. ELookUp is supposed to allow you to do that....

[June7]

ELookup uses a TOP N query to find record that meets criteria and returns value from specified field. Sort order can be critical with TOP N.

If your criteria is specific enough, why would the records need to be sorted?

[BLFOSTER]

Would just be a nice feature for my users who track attendance and need to make contact with those who are 3 weeks without. You know, bring them all to the top as the list is over 100 participants most of the time.