By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,034 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,034 IT Pros & Developers. It's quick & easy.

Find last entry which isn't null

P: n/a
PC
Hi,

I have used the DLast("[tblName]![ColumnName]","[criteria]")function
to find and display the last entry in a table. How would I go about
displaying the last record in a table that is not Null?

Thanks in advance

...pc
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
First and Last are not very useful.

Presumably you have some kind of field that you can use to determine the
last entry, e.g. an AutoNumber or a Date/Time of entry? Assuming that you
want to find the Surname from tblClient, where ClientID determines which was
the last entry:

1. Paste the function below into a module, and save.

2. Try:
=ELookup("Surname", "tblClient", "Surname Is Not Null", "ClientID DESC")
Function ELookup(Expr As String, Domain As String, _
Optional Criteria, Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. al***@allenbrowne.com
'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
Dim rs As DAO.Recordset
Dim strSql As String

'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
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"PC" <cu*****@eircom.net> wrote in message
news:8c*************************@posting.google.co m...
Hi,

I have used the DLast("[tblName]![ColumnName]","[criteria]")function
to find and display the last entry in a table. How would I go about
displaying the last record in a table that is not Null?

Thanks in advance

..pc

Nov 12 '05 #2

P: n/a
cu*****@eircom.net (PC) wrote in news:8c*************************@posting.google.co m:
Hi,

I have used the DLast("[tblName]![ColumnName]","[criteria]")function
to find and display the last entry in a table. How would I go about
displaying the last record in a table that is not Null?

Thanks in advance

..pc


strLastValue = DLast("myTable.myColumn","myTable","myColumn is not null")

--
Ross Presser -- rpresser AT imtek DOT com
.... seeking a new quote ...
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.