How to compare yes/no field with dates

[Sohaila Taravati]

I have an update query, which updates my field(Confirm) in my table to yesterday's date and puts a checkmark in my yes/no field. I have another date field(Changed) that I want to compare to my Confirm field and see if the dates are the same. If they are not the same I want to take the checkmark off of my yes/no field and keep the checkmarks in the dates that are the same, and rerun my query based on the checkmark field. I hope I didn't confuse anybody with my question I have tried several IIf statements, but no luck. I thank you for your help in advance

 

[RV]

You can use just one update statement:

UPDATE YourTable
SET YourYesNoColumn = 0
WHERE Confirm <> Changed;

RV

 

[Sohaila Taravati]

Thanks for your reply, but that does not do what I want. When I do my update, all of my yes/no fields are checked. I want my update to uncheck my yes/no field by comparing the two fields that are Confirm and Changed.

 

[raskew]

I must be missing something. It sure looks to me that RV's SQL would do what you're describing?!

 

[The_Doc_Man]

RV's statement should do it but maybe this would do it even better

UPDATE YourTable
SET YourYesNoColumn = (Confirm <> Changed) ;

 

[RV]

The_Doc_Man,

>this would do it even better<

That's a bold statement
Why is it better (and is this standard SQL)?

RV

 

[The_Doc_Man]

Better because it lacks a WHERE clause, so won't skip any records. Your code only clears the flag when the dates are different. But the original problem statement included that she also SETS the flag in her query. (It WAS a bit convoluted, but I think I'm right on that.)

And yes, that comparison is standard SQL as far as I know.

It SURELY is within Access SQL because I use something similar to it. Our database is a list of our customer's servers, over 330 and still growing. I use a query somewhat like this (only with a few more criteria in the parenthetical expression) when I'm building lists of servers that need updates applied due to recent security announcements from Carnegie-Mellon's CERT group.

 

[RV]

DocMan,

thanks for clearing out.
However, I don't agree when you're saying

"it lacks a WHERE clause"

Actually, it seems your statement contains two WHERE clauses, only they are implicit:

UPDATE YourTable
SET YourYesNoColumn = 0
WHERE Confirm <> Changed;

and

UPDATE YourTable
SET YourYesNoColumn = -1
WHERE Confirm = Changed;

RV