using if/else in an SQL query

[chris davies]

hello peeps,
I need to update a column based the values of another column. The problem I have is that the other column has four diffferent values so I am at present using two separate queries using the LIKE operator (LIKE "FT*" where the values are FT and FTunit, and NOT LIKE where the values are "PT" and "XX")

What I would like to do is nail this puppy with one query, if possible using an IF statement in the SQL. Is this possible and could some nice person give me at least an idea how to do this please?
many thanx
Chris

 

[TanisAgain]

No, you can't update with one query. You could put your criteria into SQL statements? Not the most elegant way of doing things, but will get the job done.

Dim strSql As String
Dim strsql1 As String
Dim strsql2 As String
Dim strsql3 As String

strSql = "update tblUpdate set [Field2] = '1234' where [field1] = 'FT'"
strsql1 = "update tblUpdate set [Field2] = '2345' where [field1] = 'XX'"
strsql2 = "update tblUpdate set [Field2] = '4567' where [field1] = 'PT'"
strsql3 = "update tblUpdate set [Field2] = '7890' where [field1] = 'FTUnit'"


CurrentDb.Execute strSql, dbFailOnError
CurrentDb.Execute strsql1, dbFailOnError
CurrentDb.Execute strsql2, dbFailOnError
CurrentDb.Execute strsql3, dbFailOnError

 

[chris davies]

I found my answer

Hi there peeps,
Since I only had one response, I had to do a bit more thinking and came up with this simple remedy to my problem by using the IIf function:

IIf(expression, true, false)


UPDATE tblEnrolment_3 SET Mode = IIf(FTPT Like "FT*","FT","PT");


As you can see it updates the Mode column with the value from the FTPT column with FT if the FTPT column contains FT or FTUnit, and if not either of these (if they contain PT or XX) updates the Mode Column with PT. So simple I could kick myself, but then again, I am a newbie. Job done.

Nice resource this forum.

 

[qailoh]

See? Using the search engine does answer a lot of questions. I had a situation that called for an IIf with a nested Like* and wasn't sure of the correct syntax...thanks for the tip.

 

[Moniker]

If you ever have more than just a few things to compare, avoid nesting a ton of IIF functions by using a SWITCH function instead. This:

UPDATE tblEnrolment_3 SET Mode = IIf(FTPT Like "FT*","FT","PT");

Becomes this:

UPDATE tblEnrolment_3 SET Mode = Switch(FTPT Like "FT*","FT",True, "PT");

The difference is that SWITCH allows you far more "if this then that" options without nesting. It looks like this:

YourField = SWITCH(param1, value_if_param1_is_true, param2, value_if_param2_is_true, ... paramX, value_if_paramX_is_true)


SWITCH looks at the first parameter. If it's true, then value_if_param1_is_true is returned. If it's not true, then it moves to parameter 2, and so on.

By adding "True, <somevalue>" to the end of a SWITCH function (as I did), you can guarantee a result of some sort. Since True is a keyword and it will always result in "True", that result will always be returned, and it means all your previous parameters returned "False".

To better see the usefulness of SWITCH, let's pretend this is the case:

90-100 = A
80-89 = B
70-79 = C
60-69 = D
<60 = F

Using a bunch of nested IIF statements would be a pain to write/debug/maintain. Using a SWITCH is easy, like so:

LetterGrade = SWITCH(Grade > 89, "A", Grade > 79, "B", Grade > 69, "C", Grade > 59, "D", True, "F")

SWITCH is going to stop at the first one that returns True, so a Grade of 84 will stop at parameter 2, a 63 will stop at parameter 4, and so on. The "True, "F"" at the end means the value of Grade wasn't > 59.

The equivalent IIF statement:

LetterGrade = IIF(Grade > 89, "A", IIF(Grade > 79, "B", IIF(Grade > 69, "C", IIF (Grade > 59, "D","F"),""),""),"")

You tell me which makes more sense to read.