Anniversary Date Help

[jlancton]

Hi All,

I have a db that tracks employee education.

In the Employees table, I have a HireDate field.

The Classes table hold all the classes.

The ClassesTaken table links the two keeping track of who took what and when.

What I need is a way to figure the anniversary date in the current year based on HireDate. If it is before Today, I need all classes taken between that anniversary date and today. If it is after today, I need all classes taken between the PREVIOUS anniversary date and Today.

I can't figure out how to calculate the date and pass it to a 'Between' in a query. I would think theres a way to do it without a Between, but I don't know enough SQL to even attempt it.

Naturally this is a rush, as state surveyors could walk in any day and want this info for our CNA's. Any help will be very much appreciated.

Thank you

-Jeff

 

[PHV]

In the criteria cell of ClassesTaken.when you may try this:
Between DateSerial(Year(Date())+(Format(HireDate,"mmdd")>Format(Date(),"mmdd")),Month(HireDate),Day(HireDate)) And Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jlancton]

Thanks PHV,

I put that expression in the criteria, but when I run the query, it gives me an error "Data Type Mismatch in Criteria Expression"

I checked and the fields are both dates, formatted as short date. Any ideas where else to look?

Thanks!

-Jeff

 

[lespaul]

what's the SQL of the rest of the query? (to get you open the SQL view)

 

[jlancton]

Here's what I get from the SQL view:

SELECT tblDetails.Grade, tblDetails.LastName, tblDetails.FirstName, tblClasses.ClassDuration, tblClasses.ClassName, tblClasses.ClassDate, tblDetails.AnnivDate
FROM tblDetails INNER JOIN (tblClasses INNER JOIN tblClassesTaken ON tblClasses.ClassID = tblClassesTaken.ClassID) ON tblDetails.MemberID = tblClassesTaken.MemberID
GROUP BY tblDetails.Grade, tblDetails.LastName, tblDetails.FirstName, tblClasses.ClassDuration, tblClasses.ClassName, tblClasses.ClassDate, tblDetails.AnnivDate
HAVING (((tblDetails.Grade)=4) AND ((tblClasses.ClassDate) Between DateSerial(Year(Date())+(Format("AnnivDate","mmdd")>Format(Date(),"mmdd")),Month("AnnivDate"),Day("AnnivDate")) And Date()));

 

[PHV]

And what about this ?
SELECT D.Grade, D.LastName, D.FirstName, C.ClassDuration, C.ClassName, C.ClassDate, D.AnnivDate
FROM tblDetails AS D INNER JOIN (tblClasses AS C INNER JOIN tblClassesTaken AS T ON C.ClassID = T.ClassID) ON D.MemberID = T.MemberID
WHERE D.Grade=4 AND C.ClassDate Between DateSerial(Year(Date())+(Format(D.AnnivDate,"mmdd")>Format(Date(),"mmdd")),Month(D.AnnivDate),Day(D.AnnivDate)) And Date();

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jlancton]

Okay, I put that in, replacing C, D, and T with tblClasses, tblDetails, and tblClassesTaken respectively.

When I run the query, I get two Data Type Mismatch errors, then the result window opens, with one row of ?Name in every field.

Getting closer!

-Jeff

 

[PHV]

replacing C, D, and T
Why ?
Just to know, what happens if you copy'paste my code in your SQL view pane ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jlancton]

oops, I thought it was shorthand...

I copied as originally written, and received same results. 2 Data Type Mismatch errors, and #Name? in every field.

Just FYI... Access 2002 SP3 on WinXP SP2

 

[PHV]

Could you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886