DMax to return most recent date isn't working

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

Guest

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...
 
I believe you want to use this:

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

--

Ken Snell
<MS ACCESS MVP>

timlow said:
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...
Click to expand...
 
Thanks, I got it to work, but then realized that DMax isn't the proper
function that I need because it flattens the grouping that I designated and
gave me the max date for all students instead of for the particular student.
But, Max aggregate worked after I changed some group orders around. Grouping
is great, but the order can sure be limiting! Also, it is unfortunate that
if you use an aggregate function in one field, you have to use them in all
fields!

Thanks,
Tim

Ken Snell said:
I believe you want to use this:

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

--

Ken Snell
<MS ACCESS MVP>

timlow said:
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:
On Wed, 2 Feb 2005 14:53:04 -0800, timlow

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?

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...
Click to expand...
Click to expand...
 
Back
Top