deleting wrong dates

[Eduardo]

Hello... i have an access 2007 database. I have a date field ([Sales-Date
Install #9]) with incorrect data in it.

I would like to update those fields to Null value.

here's the sql i'm using:

update STR_CRM_1_Contacts set
STR_CRM_1_Contacts.[Sales-Date Install #9] = Null
WHERE not isdate(STR_CRM_1_Contacts.[Sales-Date Install #9]);

unfortunately this doesn't work.

I have data in this field, like: 11/30/122 or 11/30/134...

I would like to get rid of those incorrect values.

so what's wrong with my query???

Any help will be appreciated.
Thanks
Eduardo

 

[Allen Browne]

Those are valid dates, so perhaps you want to clear the dates outside of a
particular range, e.g.:

UPDATE STR_CRM_1_Contacts
SET [Sales-Date Install #9] = Null
WHERE ([Sales-Date Install #9] < #1/1/1900#)
OR ([Sales-Date Install #9] > #1/1/2199#);