User generated between date parameter

[anwest75]

Hi, I'm not a newb to Access but have recently migrated to 2007 and suddenly my method doesn't seem to work. I'm generating a service award report where I need to show only those employees hitting a milestone during a certain period of time. I've had Access calculate the appropriate milestone date, and now I want to pull only those records where the milestone date is within a range of dates that the user enters. Here is an example of my WHERE clause (this is a complex query so I'm not posting all of the SQL):
WHERE (((IIf([LAWSON_EMPLOYEE].[ADJ_HIRE_DATE]>[LAWSON_EMPLOYEE].[DATE_HIRED],DateAdd("yyyy",5,[LAWSON_EMPLOYEE].[ADJ_HIRE_DATE]),DateAdd("yyyy",5,[LAWSON_EMPLOYEE].[DATE_HIRED]))) Between [Please enter start date] And [Please enter end date]) AND ((LAWSON_EMPLOYEE.COMPANY)=10) AND ((LAWSON_PAPOSITION.END_DATE)=#1/1/1700#) AND ((LAWSON_EMPLOYEE.EMP_STATUS) In ("AE","AF","AI","AO","AP","AQ","AR","AT","LD","LN" ,"LP")))
There are then several other iterations (10, 15, 20 years...to 55), each clause is separated by OR.
I would expect the user to enter 01/01/2009 for the start parameter and 12/31/2009 as the end parameter - this method has worked for me before. I've also tried 1/1/2009 to 12/31/2009 and also entering literals #01/01/2009# and #12/31/2009# as values. When entering just dates (without the hash) I get too many records, with the hash I get none. I have verified that the values returned are dates.
Any thoughts or suggestions is appreciated!
Thanks!

[anwest75]

Thanks, I figured it out on my own. The solution is setting a data type for the parameter.

[NeoPa]

An alternative would be to put the parameters, in the SQL, into CDate() function calls.

EG.

WHERE ((DateAdd('yyyy',5,IIf([LAWSON_EMPLOYEE].[ADJ_HIRE_DATE]>[LAWSON_EMPLOYEE].[DATE_HIRED],
                             [LAWSON_EMPLOYEE].[ADJ_HIRE_DATE],
                             [LAWSON_EMPLOYEE].[DATE_HIRED])) Between
        CDate([Please enter start date]) And
        CDate([Please enter end date]))
  AND  (LAWSON_EMPLOYEE.COMPANY=10)
  AND  (LAWSON_PAPOSITION.END_DATE=#1/1/1700#)
  AND  (LAWSON_EMPLOYEE.EMP_STATUS In ('AE','AF','AI','AO','AP','AQ','AR','AT','LD','LN','LP')))