David, I'm sure you know that DLookup() will work where the return value is
non-unique, so I understand you to mean that it's not a sensible thing to do
since the return value is undefined/unreliable. Fair enough (though there
are certainly cases where it does make sense, e.g. get me an invoice for
client 54--I don't care which one.)
DFirst() doesn't solve the problem I'm addressing.
None of the domain aggregate functions can answer this:
What is the most recent InvoiceID for Client 54?
where "most recent" is defined by the InvoiceDate.
If we had an extra optional argument for DLookup() to specify the sort
order, you could do that easily:
DLookup("InvoiceID", "tblInvoice", "ClientID=54", "InvoiceDate DESC")
I actully requsted MS to add this feature, arguning that an optional extra
argument breaks no existing code, and would very useful. It didn't happen,
so I wrote a replacement function that does it.
Actually, that looks incredibly obvious to me, as the arguments match your
basic SQL statement:
SELECT Arg1 FROM Arg2 [WHERE Arg3] [ORDER BY Arg4]
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns96ADAC114576dfentonbwaynetinvali@216.196.9 7.142...[color=blue]
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
> news:42f8c5b4$0$32227$5a62ac22@per-qv1-newsreader-01.iinet.net.au:
>[color=green]
>> A major weakness with DLookup() is that you cannot specify a sort
>> order, i.e. you take pot luck as to which record is returned if
>> there are multiple matches. For an extended replacement of
>> DLookup() that solves that problem by letting you specify the
>> retrieval order, see:
>>
http://allenbrowne.com/ser-42.html[/color]
>
> Pardon me, but isn't that a mis-use of DLookup(), to request
> criteria that return more than one value? Isn't that really more
> like a DFirst() function?[/color]
