Trying to replace Expired date fields with Null value 1

[PortlandSam]

Hi All!
I am trying to get a query to eliminate expired dates(Any date prior to the current one) from date fields, without removing the entire record from the table. I'm without a clue if this is even possible, and am having trouble figuring out how to get started on this. All help is warmly welcomed!
Thanks!
Sam

 

[jrbarnett]

Hello

Go to a new query, put the date field in that you want to check with a criteria value of <Date()

If you run this as is, it will show you every expired date. Now, go to the Query menu and click &quot;Update Query&quot; and put &quot;Null&quot; (without the quote marks) in the Update To box.

If you go to View then SQL, the code will look something like

Update Table Set Field = Null Where Field < Date()

where table is the name of the table and field is the name of the field.

John

 

[PortlandSam]

Works smoothly, easily, and perfectly! Thanks!!

 

[PortlandSam]

Ok, lets turn it up a bit. Now that I have the previous working, I have discovered I have a need to give a null value on the update to a couple other fields on the same record, if it's date has expired. Any idea's on that?

 

[jrbarnett]

Update Table Set Field = Null, Field2 = Null, Field3 = Null Where Field < Date()

This will update field2 and field3 to null where field's date is before todays date.

John