deleting wrong dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eduardo - 29 Jun 2007 00:29 GMT

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

Reply to this Message

Allen Browne - 29 Jun 2007 03:39 GMT

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#);

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello... i have an access 2007 database. I have a date field ([Sales-Date
> Install #9]) with incorrect data in it.
[quoted text clipped - 18 lines]
> Thanks
> Eduardo

Reply to this Message