Update table data

H

hoachen

I create a table that have a column name "CommissionPaid", and three options
selection from a form: 1-Yes, 2-No, and 3-NA. When the data is enter, it
automatically insert into table for, 1 or 2 or 3 (name: CommissionPaid). It
is difficult to understand after awhile what 1, 2 and 3 means after many
radio button and checkboxes. I want to make it easy to read in the future by
"Yes" or "No" or "NA" in the CommissionPaid column instead of value 1,2 or 3.
I am think using the update query and the link the field and update
automatically after the selection is selected. I have tried but unsuccessful
:( Is there any example or better solution to this?
Thank you anyone try to help
 
D

Duane Hookom

Perform all of your data interaction in forms where you can use a combo box
for the CommissionPaid field.
 
L

Lord Kelvan

-_- what you should do is in your queries when you select that value
have something like switch(1,"yes",2,"no",3,"N/A") and then it will
display like that if you want to do a mass update you can do
something like

update mytable set CommissionPaid = "yes" where CommissionPaid = 1

but to do that you will have to change the data type to text and as a
note it will most likly break your forms as well
 
L

Lord Kelvan

sorry let me fix that syntax for the switch statement

Switch([CommissionPaid ]= 1,"yes",[CommissionPaid ]=2,"no",
[CommissionPaid ]=3,"N/A")
 
H

hoachen

Thank you all for the help, Lord Kelvan, will this syntax add to afterupdate
the procedure or where should i add this syntex?

Thanks again.
 
L

Lord Kelvan

no you make it a query

update mytable set Switch([CommissionPaid ]= 1,"yes",
[CommissionPaid ]=2,"no",
[CommissionPaid ]=3,"N/A")

then run the query
 
H

hoachen

i c, this method work if i run the query everytime. But, i need it
automatically change when the data is enter and insert to the table. It will
be to much work to run this method, right?
 
L

Lord Kelvan

to automatically change it you will have to modify your form where
people enter the information to enter the value yes rather than the
value 1 but that query will update all current information. as i said
before you will have to also change the data type on the table to text
from number
 
Top