Return records for a Date and back 365 days

[dbar10]

I am fine tuning a database for our HR person. I have run into a problem that has me stumped. I need for the query to return employee records for a date of an attendance offense and all records for events 365 days back. Below is what I have now, however Access says that the expression is too complex. I know that this should be easy but for the life of me I can't figure out how to make it work. I really appreciate any help you could provide. Seems like I have tried 25 or more ways to do this and nothing works. Thanks

SELECT DISTINCTROW EMASTER.FILENO, EHNOTTM.TRDATE, EHNOTTM.REMARKS, EHNOTTM.ACODE, EHNOTTM.EXCEPTION, EHNOTTM.SEQ, EHNOTTM.ECODE, EHNOTTM.FMLATIME, EHNOTTM.OCC, EHNOTTM.HRSMISS, IIf([EXCEPTION]="C",1,0) AS Expr7, EHNOTTM.ROCC, EHNOTTM.TOCC, EHNOTTM.TERM, EMASTER.STATUS, EMASTER.NAME, EMASTER.SHIFT, EMASTER.HIRE_DATE, EMASTER.TERM_DATE, EMASTER.TERM_REAS, EMASTER.TERM_DOC, IIf([EXCEPTION]='Y',[OCC]*1,0) AS Expr1, IIf([EXCEPTION]='N',[OCC]*1,0) AS Expr2, IIf([EXCEPTION]='V',[OCC]*1,0) AS Expr3, IIf([EXCEPTION]='Y',[HRSMISS]*1,0) AS Expr4, IIf([EXCEPTION]='N',[HRSMISS]*1,0) AS Expr5, EMAST_2.ANN_DATE, EMAST_2.CURR_VAC, EMAST_2.CURR_USED, EMASTER.HIRE_DATE, EMASTER.VIOLATIONS, EMASTER.WARN1, EMASTER.WARN2, EMASTER.DOC1, EMASTER.DOC2, EHNOTTM.DEPT, IIf([Exception]='C',[ROCC]*1,0) AS Expr6, EMAST_2.VAC_DATE, EMASTER.INACTIVE
FROM (EMASTER LEFT JOIN EHNOTTM ON EMASTER.FILENO = EHNOTTM.FILENO) INNER JOIN EMAST_2 ON EMASTER.FILENO = EMAST_2.FILENO
WHERE (((EMASTER.FILENO)=[Forms]![EmployHistSearch]![FILENO]) AND ((EHNOTTM.TRDATE)<=[Forms]![EmployHistSearch]![OffenseDate] And (EHNOTTM.TRDATE)>=[Forms]![EmployHistSearch]![OffenseDate]-365) AND ((EMASTER.STATUS)="A") AND ((EMASTER.INACTIVE)=No))
ORDER BY EHNOTTM.TRDATE, EHNOTTM.EXCEPTION;

 

[dhookom]

Is EHNOTTM.TRDATE a date/time field?
What is the data type of EMASTER.INACTIVE?

Have you attempted to specifiy the data types of OffenseDate?

Have you considered renaming Expr1 - Expr6 to names that mean something?




Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Yes the EHNOTTM.TRDATE is a date field. This is the date of the attendance infraction.

The EMASTER.INACTIVE field is a Yes/No
The Expr1 - Expr6 were setup by the person that originally built this database and are referred to in other places. Might be a little difficult to make any change to them now.
I will tell you that I have tried querying the date with the same expression and others on a query that is much smaller and still get the sam eror message. It does not know how to subtract 365 days from the User inputted date.

 

[dbar10]

Never mind, I found the problem. In the form date field I had formateed for short date with an input mask for a 2 digit year. Going back to the properties I found that in the format bbox it had been cleared after putting in the input mask. So I entered the short date format again and now it works. I knew it had to be something simple but sometimes those can be the hardest to track down. Thanks for your help.