update Yes/No field from yes to no

I have a access 2003 database table with a Yes/No field called Flag.

When the user is in the database, they check the field in order to
flag that field for printing. Before they exit the database I'd like
to have a query run and change all the "yes" flags to "no" so the
next time they use the database all records are cleared from printing.

I tried creating a macro to run a sql command to set flag="no" where
flag="yes" but that returns a datatype mismatch error.

I've also tried an update query, but I am not very good at those.

Can anyone provide some best practice way to handle this update?

Re: update Yes/No field from yes to no

"LFM" wrote in message
news:1194565925.068644.165200@k35g2000prh.googlegroups.com.. .
>I have a access 2003 database table with a Yes/No field called Flag.
>
> When the user is in the database, they check the field in order to
> flag that field for printing. Before they exit the database I'd like
> to have a query run and change all the "yes" flags to "no" so the
> next time they use the database all records are cleared from printing.
>
> I tried creating a macro to run a sql command to set flag="no" where
> flag="yes" but that returns a datatype mismatch error.
>
> I've also tried an update query, but I am not very good at those.
>
> Can anyone provide some best practice way to handle this update?

Usually, "Yes/No" fields are integer types.
0 = No
-1 = Yes

So you should create your update query as:
UPDATE Table1 SET Flag = 0 WHERE Flag=-1

Since you're changing everything to No, you don't really need the WHERE
portion.
Fred Zuckerman

Re: update Yes/No field from yes to no

On Nov 8, 7:10 pm, "Fred Zuckerman" wrote:
> "LFM" wrote in message
>
> news:1194565925.068644.165200@k35g2000prh.googlegroups.com.. .
>
> >I have a access 2003 database table with a Yes/No field called Flag.
>
> > When the user is in the database, they check the field in order to
> > flag that field for printing. Before they exit the database I'd like
> > to have a query run and change all the "yes" flags to "no" so the
> > next time they use the database all records are cleared from printing.
>
> > I tried creating a macro to run a sql command to set flag="no" where
> > flag="yes" but that returns a datatype mismatch error.
>
> > I've also tried an update query, but I am not very good at those.
>
> > Can anyone provide some best practice way to handle this update?
>
> Usually, "Yes/No" fields are integer types.
> 0 = No
> -1 = Yes
>
> So you should create your update query as:
> UPDATE Table1 SET Flag = 0 WHERE Flag=-1
>
> Since you're changing everything to No, you don't really need the WHERE
> portion.
> Fred Zuckerman


Thank you very much.