Using DLast with criteria (1 Viewer)

[jimtimber]

Hi,

I'm trying to generate a list of employees who haven't had a review since Dec 2013.I'm using this expression in my query:

Last review: DLast("[ReviewDate]","[Tbl_OutcomeHistory]")

which brings up the last date the employee had a review.(I tried using DMax but it didn't seem to like dates?)

How do I get it to just show those who haven't had a review since 1st Dec 2014. I thought I could use "<01/12/2014" in the criteria but it returns no values. I've tried to drop the date into the expression but that returns no values.

Any ideas?

Thanks,

Jamie

 

[KenHigg]

Just a guess but you may need to qualify the date with #s

<#01/12/2014#

 

[plog]

DLast should be removed as a function from Access. It doesn't do what anybody thinks it does. You should use Dmax, it does work with dates. What makes you say it doesn't? can you provide an example? Is [ReviewDate] actually a Date/Time field?

In either case (Dlast or DMax) Ken is correct, surround your dates with #'s. However, Since December 1st, 2014 hasn't occured, my guess is nobody has had a review "since" then.

 

[jimtimber]

Thanks Ken, I tired that before but it returns no values when I add that to the criteria. (I meant <2013, not 2014)

Thanks again.

 

[plog]

Now that I think about it, why are you using a domain function (Dmax, Dcount, etc.) to get this? Why not an aggregate query?

SELECT PersonID, MAX(ReviewDate) as LastReview
FROM Tbl_OutcomeHistory
GROUP BY PersonID

 

[namliam]

What is the rest of your query?

I mean cant you simply do:
Select EmployeeID, max(Reviewdate)
From Tbl_OutcomeHistory
Group by EmployeeID
having Max(Reviewdate) < #12/01/2013#

Edit Plog crossposted and beat me by a minute (or less )

Edit2: Note the use of US formatting in the date, Dec 01 2013, which given todays date I am assuming you mean instead of the 2014 date

 

[jimtimber]

Hi Plog, the Review Date is a time/date field.

If I use:

Last review: DMax("[ReviewDate]","[Tbl_OutcomeHistory]")

when I run the query it brings me a huge list of records.

When I add <#01/12/2013# it returns no records. I know there are employees who's review date is less than 1/12/13. This is confirmed when I run the query without the date criteria as I can see the records.

 

[KenHigg]

You may need:

CDate(DMax("[ReviewDate]","[Tbl_OutcomeHistory]"))

??

 

[jimtimber]

Now that I think about it, why are you using a domain function (Dmax, Dcount, etc.) to get this? Why not an aggregate query?

SELECT PersonID, MAX(ReviewDate) as LastReview
FROM Tbl_OutcomeHistory
GROUP BY PersonID

This worked! Thank you Plog. I have now informed my boss that DLast doesn't do what people think.

Thanks to all you guys for help suggested...I'm in my 4th week of using Access so struggling with some simple stuff.

Jamie

 

[namliam]

DLast does do what everyone expects it to do...

Problem is people dont understand that a "standard" select doesnt guarantee any form of order unless you explicitly give it one by using
Select... from .... ORDER BY ...

If you have such a query, DLast works wonders ...
My "thing" is any function starting with D (as in DCount Dmax etc) should be removed as for 99% of the time they are used they are misused/mistreated/abused etc....

 

[plog]

DLast does do what everyone expects it to do...Problem is people dont understand...

You didn't even finish your sentence before you contradicted yourself.

DLast doesn't do what people expect. That statement is unequivocally true. Stay focused, namlian, my statement is a testament to people's expectations, not to how DLast operates. So any technical arguments you make about DLast are irrelevant.

Dlast doesn't operate as people expect.

 

[KenHigg]

Dlast doesn't operate as people expect.

Neither do people

 

[namliam]

what Dlast (or Dfirst for that matter) do or dont
vs
what a select (or even a table fetch) does

Those are 2 different things.... with regular intervals there are questions like
"why is my table not in order any more?"
or
"why isnt my query in order of its IDs?"
etc...

Not only on this forum, but also on Oracle and SQL forums I visit.... people dont know the basics thats where it goes wrong.... Not their understanding (or lack there off) of Dlast or whatever function.

result is the same, problem is different.