Using "Last" to return last record in result set, not working. Why

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing a query that will return a list of students with their most
recent major that they have declared. To do this, I have set up the query to
group and sort the records ascending, by "Name", then by term "Begin Date."
Then, the query is supposed to return the Last of "Begin Date" before (<=) a
[user defined parameter date]. However, it returns all the records before
the date instead of just the most recent record. Am I doing something wrong?
 
I am designing a query that will return a list of students with their most
recent major that they have declared. To do this, I have set up the query to
group and sort the records ascending, by "Name", then by term "Begin Date."
Then, the query is supposed to return the Last of "Begin Date" before (<=) a
[user defined parameter date]. However, it returns all the records before
the date instead of just the most recent record. Am I doing something wrong?
Click to expand...

Merely assuming that Last() means what you would think it ought to
mean.

The Last operator in Totals queries is all but useless. It returns the
last record *IN DISK STORAGE ORDER* - an order over which you have no
control, and which does not respond to any sort clauses you might have
put on the query.

You'll need to use DMax() to find the largest value of the field less
than the defined date.

John W. Vinson[MVP]
 
Thanks, I guess Last is pretty much useless. So, I tried as you suggested,
using the DMax function. I created a calculated field and entered various
versions of the following expression:

DMax([Begin Date], "tblCredits", <=[Enter start date of current quarter])

It wouldn't work. I also tried:

DMax([Begin Date], "tblCredits", [Begin Date]<=[Enter start date of current
quarter])

It would say that the above expression are either incorrect or too complex
to evaluate. Any suggestions?

John Vinson said:
I am designing a query that will return a list of students with their most
recent major that they have declared. To do this, I have set up the query to
group and sort the records ascending, by "Name", then by term "Begin Date."
Then, the query is supposed to return the Last of "Begin Date" before (<=) a
[user defined parameter date]. However, it returns all the records before
the date instead of just the most recent record. Am I doing something wrong?
Click to expand...

Merely assuming that Last() means what you would think it ought to
mean.

The Last operator in Totals queries is all but useless. It returns the
last record *IN DISK STORAGE ORDER* - an order over which you have no
control, and which does not respond to any sort clauses you might have
put on the query.

You'll need to use DMax() to find the largest value of the field less
than the defined date.

John W. Vinson[MVP]
Click to expand...
 
timlow said:
Thanks, I guess Last is pretty much useless. So, I tried as you suggested,
using the DMax function. I created a calculated field and entered various
versions of the following expression:

DMax([Begin Date], "tblCredits", <=[Enter start date of current quarter])

It wouldn't work. I also tried:

DMax([Begin Date], "tblCredits", [Begin Date]<=[Enter start date of current
quarter])
Click to expand...

The third parameter to DMax must be a string. Something like

DMax([Begin Date], "tblCredits", "[Begin Date] <= #" & [Enter start date of
current quarter] & "#")

may do what your after. Or even

DMax([Begin Date], "tblCredits", "[Begin Date] <= " & format([Enter start date
of current quarter], "\#mm/dd/yyyy\#")

to avoid the must-be-american-date-format issue.

-Greg.
 
Gregory said:
timlow said:
Thanks, I guess Last is pretty much useless. So, I tried as you
suggested, using the DMax function. I created a calculated field and
entered various versions of the following expression:
DMax([Begin Date], "tblCredits", <=[Enter start date of current quarter])

It wouldn't work. I also tried:

DMax([Begin Date], "tblCredits", [Begin Date]<=[Enter start date of
current quarter])
Click to expand...


The third parameter to DMax must be a string. Something like
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

All parameters to domain aggregate functions are strings:

DMax("column name", "domain name", "criteria")

Domain name is the query/table name.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgK33oechKqOuFEgEQJMVgCgwaSEakj+pohspeDvi7n0YT/i7KEAoOsp
i5vR/kWEMzmQVWGTxbBLnvUQ
=KmJ1
-----END PGP SIGNATURE-----
 
Back
Top