Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hotkeychick
Starting Member
1 Post |
 Posted - 2003-08-19 : 21:49:10
|
| Hello, I'm new here! I don't know how to write SQL that well so please help!In Access 2002, I was trying to update field "Age Status" based on the IIF function (more than one conditional expression). For instance, if DOB>=1/1/2003, then I want Age status update "infant", and if DOB between 1/1/1987 and 12/31/2002, then update Age status as "teenager", and if DOB between 1/1/1983 and 12/31/1986, then update Age Status with "Adult", and so forth. Here's my data:DOB:1/1/20031/1/19871/1/19831/1/1970 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-19 : 22:21:18
|
| You can also use the Switch() function, it's a little more compact than nested Iif()'s:UPDATE myTable SET [Age Status]=Switch(DOB>=#1/1/2003,"Infant",DOB>=#1/1/1987#,"teenager",#1/1/1983#,"Adult",True, "Old Fart")You can add as many date values as you like, and a corresponding category for each, just make sure to maintain the order as shown above. Switch() will return the FIRST expression that evaluates as true, even if it's not the one that's most appropriate, so you need to construct the logic so that it "falls through" each previous option (this works like the switch statement in C/C++/Java)The reason for the "True" at the very end is to provide the ultimate replacement expression. If all other tests are false, this will be the value that gets substituted. It's always a good idea to have such an expression in Switch() because you may get an error if somehow NONE of the logical expressions evaluate as True.An unrelated word of advice: do yourself a favor and DON'T put spaces in your column names. Or punctuation or any other non-alphanumeric character. You are far better off naming it "AgeStatus" or even "Age_Status". Someday you may end up using another database system that is far less tolerant of spaces in table or column names, and you'll save yourself a lot of work later on if you get rid of them now. |
 |
|
|
|
|
|
|
|
Update If,Then, statement