Dlast not functioning as expected 1

[Moss100]

Hello All,

Have a table which records applicants for a property.

I want to find the last input applicant for a certain property (in the code below - property 1762).

I have tried the code below, but this is not returning the last entered applicant for the property. Can someone help?

Many thanks Mark

 =DLast("[App_ID]","tbl_Applicant","[App_Property_ID_Link] = " & [Prop_ID])

 

[Andrzejek]

Isn't [tt]DLast[/tt] function the same as saying:
[tt]
Select Max(App_ID) As MyMaxAppID
From tbl_Applicant
Where App_Property_ID_Link = 1762[/tt]



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

DLast() shouldn't exist. As records are created it's like throwing marbles in a box. There is not order so first and last make no sense. If you have a date/time the records were created it could help resolve your question.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

>first and last make no sense.
True. Unless YOU specify the meaning of First and Last, which means YOU put the records (your 'marbles in a box') in Order By [some field(s)]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Andy, I think you need to write a small DLast() alternative with an "Order By" argument. Maybe name it DLastest()

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Funny guy
In my opinion, Access programmers rely too much on build-in functionality, with all DLookup, DMax, DMin, and whole bunch of others. Myself, I would rather write my own, or - in most cases - let the data base do all heavy lifting. SQL has all MIN(), MAX() etc. build in, so I use it, and I have a full control of what I need.
I do understand the ease of use of build-in functions in Access, but then we have the issues like Moss100 has.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

I've used the following which seems to work fine:

= DMax("[App_ID]","tbl_Applicant","[App_Property_ID_Link] = " & [Prop_ID])


Thanks all - I will avoid Dlast from now on.

 

[Moss100]

How's this?

'database custom dlookup function
Function fnc_DB_DLAST(ByVal Expression As String, _
ByVal Domain As String, _
Optional ByVal criteria As String, _
Optional ByVal SortOrder As String) As Variant

On Error Resume Next

Dim strSQL

strSQL = "SELECT TOP 1 " & Expression & " FROM " & Domain

If criteria <> "" Then strSQL = strSQL & " WHERE " & criteria

If SortOrder <> "" Then strSQL = strSQL & " ORDER BY " & SortOrder

fnc_DB_DLAST = CurrentProject.Connection.Execute(strSQL)(0)


End Function

 

[Andrzejek]

Just a side note...
I don't think you need to use square brackets [ ] since you don't have Spaces in the fields' names and you don't use reserved words.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

[tt]On Error Resume Next[/tt]
That is dangerous.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I typically use faq701-4233 which allows you to enter any valid SQL statement that returns a single value.

Concatenate("SELECT TOP 1 App_ID FROM tbl_Applicant WHERE App_Property_ID_Link = " & [Prop_ID])

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

On Error Resume Next - Sorry!!

 

[Moss100]

Thank you Duane.