If then else statement help

[awveres]

Hello, I am working on an if then else statement to show if hours contain the earnings code health sick or vacation. If it contains the earning code SICKT, HOLTK, VACKTK i would like it to display the current hours for that employee.

IF ([Earnings Code] contains (SICKT, HOLTK, VACTK')) THEN ([Current Hours]) ELSE ('0')

How do i make this statement display the value in the current hours column if it contains one of these earnings code?

[dougp]

You need quotes around the values.

[awveres]

Sorry, my statement is IF ([Earnings Code] contains ('SICKT, HOLTK, VACTK')) THEN ([Current Hours]) ELSE ('0')

This statement validates, but does not show the hours if they are SICKT, HOLTK, VACTK

[bus_pass_man]

You need to understand that your expression is searching for the string SICKT, HOLTK, VACTK.   If that string is found then current hours should be returned.  If it is not then the string 0 should be returned.

You do not clearly state what is happening but my surmise is that the string 0 is being return always.

If that is the case then it would appear to be that your data does not contain the string SICKT, HOLTK, VACTK but rather values such as SICKT and HOLTK and VACTK. If that is the case then you would need to adjust your expression accordingly.

You might also need to think about data type mismatching between current hours and your else value of the string 0.

[awveres]

"You do not clearly state what is happening but my surmise is that the string 0 is being return always."

This is what is currently happening, what changes should be made to the string to look for each, SICKT or HOLTK or VACTK?

[bus_pass_man]

As dougp said,

You need quotes around the values.

This is fairly elementary stuff. The fact that you are having difficulty is a cause for concern. Consequently, if you were to somehow manage to get yourself into a more complex situation it is not clear, given the admittedly limited mental model of you qua query writer which is available to me given that its source material is, because this seems to be your first question, necessarily restricted only to the contents of this thread, that you would then be in position to recognize when things are subtly going wrong. I'm trying to avoid a certain 4 letter acronym but some investment of time in reading about SQL syntax could be profitable for you.

In addition it is not clear you want to be using strings. Hours suggests numeric values. I was trying make that point in a less direct, brutal manner in my previous response.

[dougp]

"You do not clearly state what is happening but my surmise is that the string 0 is being return always."

This is what is currently happening, what changes should be made to the string to look for each, SICKT or HOLTK or VACTK?

Quote the values.  Maybe take a basic, 10-minute SQL tutorial.

[MFGF]

"You do not clearly state what is happening but my surmise is that the string 0 is being return always."

This is what is currently happening, what changes should be made to the string to look for each, SICKT or HOLTK or VACTK?

What Doug suggested is that you include each value in quotes

'SICKT', 'HOLTK', 'VACTK'

The pitfall bus_pass_man identified is the data type of the [Current Hours] item may be different than the literal '0' you are using in the expression. If [Current Hours] is a character data type, then '0' is appropriate. If it is a numeric data type, then 0 (without the quotes) would be appropriate.

Cheers!

MF.

[BigChris]

I might be overthinking this, but given that OP is asking for stings that contain those literals, would he need something like

Code Select Expand

IF ([Earnings Code] contains 'SICKT'
 or [Earnings Code] contains 'HOLTK'
 or [Earnings Code] contains 'VACTK')
THEN ([Current Hours])
ELSE (0)