Pulling records based on User Date minus a year

[dbar10]

I have a query field called TRDATE that is the date of an attendance offense. I need to retrieve all records based on the report date minus a year. Between Date()-365 And Date() doesn't work because my report date my be a week or more ago based on payroll periods. Please help. Thanks

 

[PWise]

Well then it will be

between Reportdate -365 and reportdate

 

[dbar10]

Reportdate is [Enter Report Date;]. The value has to be entered twice?

 

[dbar10]

Between [Enter Report Date]-365 And [Enter Report Date]. This expression is too complex according to access

 

[dbar10]

This is the query that is running the report:

SELECT DISTINCTROW EMASTER.FILENO, EHNOTTM.TRDATE, EHNOTTM.REMARKS, EHNOTTM.ACODE, EHNOTTM.EXCEPTION, EHNOTTM.SEQ, EHNOTTM.ECODE, EHNOTTM.FMLATIME, EHNOTTM.OCC, EHNOTTM.HRSMISS, 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
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) Between ([Forms]![EmployHistSearch]![RptDate]-365) And [Forms]![EmployHistSearch]![RptDate]))
ORDER BY EHNOTTM.TRDATE, EHNOTTM.EXCEPTION;

This worked fine until I put in the report date part.

The part that I am having trouble with is this:

Between ([Forms]![EmployHistSearch]![RptDate]-365) And [Forms]![EmployHistSearch]![RptDate]))

I get a message saying:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I'm scratching my head. Will you please help? Thanks.

 

[PHV]

What about this WHERE clause ?
WHERE EMASTER.FILENO=[Forms]![EmployHistSearch]![FILENO] AND CLng([Forms]![EmployHistSearch]![RptDate]-EHNOTTM.TRDATE) Between 0 And 365


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

Thanks you PHV. I changed it to your suggestion but I am still getting the same error message.

SELECT DISTINCTROW EMASTER.FILENO, EHNOTTM.TRDATE, EHNOTTM.REMARKS, EHNOTTM.ACODE, EHNOTTM.EXCEPTION, EHNOTTM.SEQ, EHNOTTM.ECODE, EHNOTTM.FMLATIME, EHNOTTM.OCC, EHNOTTM.HRSMISS, 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, CLng([Forms]![EmployHistSearch]![RptDate]-[EHNOTTM].[TRDATE])
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 ((CLng([Forms]![EmployHistSearch]![RptDate]-[EHNOTTM].[TRDATE])) Between 0 And 365))
ORDER BY EHNOTTM.TRDATE, EHNOTTM.EXCEPTION;


error message: This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Have any other ideas?