A97 table design?

[MLH]

If I open an A97 table, resort its key-field to descending order and
attempt to close the table, A97 asks me if I wish to save the table
DESIGN? Now really, I don't think the table design is being saved
at all. If so, how does one explain these facts:

1) say I do click Yes to save the table design
2) later I open the table manually and, sure enough, key field is
shown in descending order
3) I run this in debug window... ?DLookup("[KeyField]", "MyTable")

Step 3 returns the value of the FIRST actual record ever entered
into the table - not the last. Obviously, if fundamental table DESIGN
had been saved as implied by the A97 prompt, the last would-a-been
shown. So, A97 is NOT saving a modified version of table design, is
it? Its merely saving a query filter sort. Sound about right?

[Allen Browne]

Only the table's OrderBy property is being saved.

By definition, records are unordered in a relational database. The fact that
Access presents them in primary key order by default is understandable, but
it is outside of what a relational database should do.

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

--
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.

"MLH" <CR**@NorthStat e.net> wrote in message
news:2l******** *************** *********@4ax.c om...

If I open an A97 table, resort its key-field to descending order and
attempt to close the table, A97 asks me if I wish to save the table
DESIGN? Now really, I don't think the table design is being saved
at all. If so, how does one explain these facts:

1) say I do click Yes to save the table design
2) later I open the table manually and, sure enough, key field is
shown in descending order
3) I run this in debug window... ?DLookup("[KeyField]", "MyTable")

Step 3 returns the value of the FIRST actual record ever entered
into the table - not the last. Obviously, if fundamental table DESIGN
had been saved as implied by the A97 prompt, the last would-a-been
shown. So, A97 is NOT saving a modified version of table design, is
it? Its merely saving a query filter sort. Sound about right?

[David W. Fenton]

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in
news:42******** *************** @per-qv1-newsreader-01.iinet.net.au :

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

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?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

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("Invoic eID", "tblInvoice ", "ClientID=5 4", "InvoiceDat e 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" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** **********@216. 196.97.142...

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in
news:42******** *************** @per-qv1-newsreader-01.iinet.net.au :

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

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?

[Trevor Best]

Allen Browne wrote:

Actually, that looks incredibly obvious to me, as the arguments match your
basic SQL statement:
SELECT Arg1 FROM Arg2 [WHERE Arg3] [ORDER BY Arg4]

my tLookup can do the same, e.g.

tLookup("field" ,"table","field 2='whatever' order by field3 desc")

;-)

[Allen Browne]

And yours has been around longer, Trevor.

BTW, you don't provide the link to your site in your sig?
Maybe that's what "Windows is searching" was all about. :-)

--
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.

"Trevor Best" <no****@nospam. invalid> wrote in message
news:42******** *************** @news.zen.co.uk ...

Allen Browne wrote:

Actually, that looks incredibly obvious to me, as the arguments match
your basic SQL statement:
SELECT Arg1 FROM Arg2 [WHERE Arg3] [ORDER BY Arg4]

my tLookup can do the same, e.g.

tLookup("field" ,"table","field 2='whatever' order by field3 desc")

;-)

[Trevor Best]

Allen Browne wrote:

And yours has been around longer, Trevor.

BTW, you don't provide the link to your site in your sig?
Maybe that's what "Windows is searching" was all about. :-)

What sig? <g>

My trusty old Soundblaster went pop and took Windows out with it so no
sig at all ATM until I get around to setting one up again.

I used to have a link about 7 years ago, about the time I last updated
the site :-)