If statement in query

[jamphan]

I would like to insert an if then type variable in my query or when using SQL it is called case. Basically I want to say if MyField = 0 then Lost, if MyField = 1, then Stolen and so on like that. Is this possible? Thanks for the help.

 

[KenHigg]

Are you doing this in Access's query builder?

If so:

MyNewFldName: iif(MyField = 0,"Lost", "1")

kh

 

[jamphan]

I am getting an error saying the expression is too complicated. I used the exact phrasing in your example. Any ideas on the problem? I do have 5 different numbers that I need to convert to a word if it is true.

 

[KenHigg]

What is the exact name of the fld you're trying to convert?

kh

 

[jamphan]

numType Loss

 

[KenHigg]

MyNewFldName: iif([numType Loss] = 0,"Lost", "1")

???
kh

 

[jamphan]

That's what I had. It says my expression is typed incorrectly or too complex to evaluate. What is the purpose of the "1" in the expression?

 

[KenHigg]

MyNewFldName: iif([numType Loss] = 0,"Lost", "1")


iif(Expression to evaluate,If expression is true show this,If expression is false show this)

kh

 

[jamphan]

It is just not working. This is sort of what I want to see happen:

Loss: IIf([numType Loss]=0,"Lost",IIf([numType Loss]=1,"Stolen"," "))

I am not sure if this is the correct syntax to add other variables or not. I want it to return Lost if it is a 0 or Stolen if it is 1 otherwise return nothing. I get the same error message when I use the formula you gave in the initial response. Thanks for helping on this. I know it can be done.

 

[KenHigg]

You are on the right track, embedding the iif()'s. But let's see if we can get one to work first...

Say you can't get one to work or you can?

(I assume you are of aware that the space in the fld name is a no-no?)

kh

 

[jamphan]

I can not get anything to work with this formula. I do know it is a no-no to have a space in the field name.

 

[KenHigg]

So in your query, do you have just one table that it's drawing it's data from? Some times if you have more than one table (or query) that has a fld with the same name you'll have problems... (table 1 and table 2 has a fld with the same name)

kh

 

[jamphan]

No, it is just one table. I do have a form setup that allows the user to select what criteria they would like to see such as a 0 or 1 which I would like to change over to Lost or Stolen. Also, the result does not necessarily have to be in my query. I really just need it on the report because that is all the user will see. I tried the formula you sent on a query with my TypeLoss field and the new field column returned all errors#.

 

[KenHigg]

We're just missing something simple here. Give me a minute to come up with an example to post...


kh

 

[KenHigg]

See if these help...

kh

 

[jamphan]

I am thinking that trying to convert a number to text could be the problem. What I have is exactly like your example except for the numbers I am using.

 

[KenHigg]

Is [numType Loss] a text type data fld? You may need this:

MyNewFldName: iif([numType Loss] = "0","Lost", "1")

kh

 

[jamphan]

That's it. Thanks for all of the help!!!!

 

[KenHigg]

'
'
'
Cool!

FYI: the 'num' in 'numType Loss' threw me...

 

[jamphan]

It used to be a number field and I recently changed it to a text. It was throwing me as well. Thanks again.