update query question

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I would like to use an update query to fill in a column.
If Col A > 500, then Col Z = TRUE but if Col A < 500, then
Col Z = FALSE.

Is this possible to do with one query? I'm not sure how
to complete the "update to" field for Col Z.

I can do what I want with 2 update queries (1) If Col A >
500, then Col Z = TRUE. (2) If Col Z is null, then Col Z
= FALSE.

There must be a better way. Any help would be
appreciated. Thanks.
 
Try entering a formula in the update to section for Col Z. Your two examples
give slightly different criteria though, one checks for Col A < 500, the
other checks for null (neither addresses the case of Col A = 500). But, I
think all will be accounted for if you use:

iif([Col A]>500,True,False)

but, replace [Col A] with the actual fieldname for Col A (if that isn't the
actual fieldname).

HTH, Ted Allen
 
Dear JT:

Your specification may be incomplete. If the value of ColA is exactly
500, what do you want for the result in ColZ?

Basically, you can use IIF to test ColA and return TRUE/FALSE. If
ColZ is boolean then 0 is false and -1 is true, so the expression
would be:

CBool(IIF(ColA >= 500, -1, 0))

I added CBool (convert to boolean) mainly for clarity. If your ColZ
is boolean, it would convert anyway.

Drop the "=" above if you want 500 to be false instead of true.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I would suggest :

[Col Z] = ([Col A] > 500)

This of course, assuming that if Col A = 500 then Col Z = false

Take Care
Mauricio Silva
 
Back
Top