adding a new nonexisting field to a make table query

[frustrated]

I have a query that updates my main table based on linked tables on my
server. What I want to do is classify each record based on their return
status. I have othe queries that will fill in that filled based on criterias
that look at the other fieldsin the table. So how do you a another field to
the table that the other queries can update the value in that blank feild
base on their criteria?

Ex: record 1 has a returned date and was restocked so I want to classify it
as RR
record 2 has a rebill date so I want to classify it as RB
record 3 has no returned date nor a reibilled date so I want to
classify it as NR
record 4 has a returned date and was destroyed so I want to classify
it as RD

I can do this dirtly by after updating the table, relabel the Expr:1 field
as code and rerunning the latter queries. But I need a clean way that I can
assign an auto run

 

[Michel Walsh]

Hi,



UPDATE myTable
SET status = SWITCH(
(Not ReturnedDate IS NULL) AND isRestored, "RR",
(Not Rebill IS NULL), "RB",
(Rebill IS NULL AND ReturnedDate IS NULL, "NR",
(Not ReturnedDate IS NULL) AND isDestroyed, "RD",
True, NULL)
WHERE status IS NULL




Note that a SWITCH arguments work by pair (see help file) and the first
comparison that evaluates to true get its companion returned. So, basically,
if the condition is not covered, here, in the first 4 cases, the fifth,
which evaluates to true, will thus return NULL.



I also added a condition to NOT UPDATED status that are already evaluated.
In other words, if status is null, that means it is still to be evaluated,
and that means "status" allows null value.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Missing a closing parenthesis in the fifth argument (third comparison).


Vanderghast, Access MVP