date criteria is not working

[kwooten]

I dont know what the deal is with the date criteria.. I am still getting dates outside the date range i am asking for..

SQL:

SELECT HUMANIC_CPO_EMP_ALL.DIV_1_DESC, HUMANIC_CPO_EMP_ALL.DIV_2_DESC, HUMANIC_CPO_EMP_ALL.ENUMBER, HUMANIC_PERFHIST_MASTER.EFFDATE
FROM HUMANIC_CPO_EMP_ALL RIGHT JOIN HUMANIC_PERFHIST_MASTER ON HUMANIC_CPO_EMP_ALL.SSN = HUMANIC_PERFHIST_MASTER.HSSN
WHERE (((HUMANIC_PERFHIST_MASTER.EFFDATE)>#1/1/2009#))
ORDER BY HUMANIC_CPO_EMP_ALL.DIV_1_DESC, HUMANIC_CPO_EMP_ALL.DIV_2_DESC;

[rpeare]

WHERE (((HUMANIC_PERFHIST_MASTER.EFFDATE)>#1/1/2009#))

so you're getting records where the EFFDATE on the table HUMANIC_PERFHIST_MASTER is prior or equal to 1/1/2009? You say you're getting records outside of your range but really you only have one endpoint, a range would be between two dates.

[kwooten]

well i want records for dates newer than 1/1/2009 so i used >=#1/1/2009# and that didnt work, I was still getting dates from 2004. I tried >#1/1/2009# and <#4/2/2011# also and that didnt work either

[rpeare]

are you storing this field as a text field? if so that's why

try doing

WHERE ((cdate(HUMANIC_PERFHIST_MASTER.EFFDATE)>#1/1/2009#))

[kwooten]

EFFDATE is Date/Time...

[rpeare]

can you upload a sample database with garbage data. If it's date time I don't understand how you would be getting items outside of your criteria.

[kwooten]

i created a new test database and it worked in that one so I am going to redo this one i am working on.