iif with multiple criteria

[amyxkatexx]

Okay, so I am VERY new to Access and as a matter of fact, I don't even have it installed on my computer and only have limited access to a computer that does have it, so I do all my research beforehand so as to not waste time. This makes things rather difficult to "test" while I am finding answers online.

Basically what I know (and this may be wrong) is that someone has set up a table that is directly linked to the company's database. I then import an excel spreadsheet of my own as a table and then link it accordingly to the table so I can output data from the database that is not on the spreadsheet.

This works great except for one thing. One of the fields I am trying to get out of our database may have several values per item. I only care about 3 of these values, 883, 879, and 884. It is possible that one item may have all 3 of these values, but unlikely. In that situation, I would prefer it either output each one or if possible only one with the hierarchy being 883, 879, 884.

What I have done thus far is place "883 or 879 or 884" in the criteria section but the problem with this, is it only returns items that have these values and not ones that do not have any value or have a different value.

Basically, what I want is an an if-then statement like this:

If
[ListKey] = 883 Then
result = "883"
Else If
[ListKey] = 879 Then
result = "879"
Else If
[ListKey] = 884 Then
result = "884"
Else
result = ""
End If

How would that be written and where would I put it for it to work as I am intending without using macros.

Thanks in advance!

-Amy Kate

 

[JPearson]

Are you trying to filter the results to only contain those values or to get only those values to display if they are present in the [listkey] field of the results?

 

[JPearson]

Im sure the syntax isnt exact since this is from my DB2 query but this might do what you want modified in the SQL

case
when [listkey] in (883) then '883'
when [listkey] in (879) then '879'
when [listkey] in (884) then '884'
else ''
end As Whatever

 

[boblarson]

Sounds like you have multiple values stored as one field which means your database is not normalized. You should not be storing multiple values in one record's field. If you have multiple data it should go in another table.

 

[amyxkatexx]

Yes and no.

Say I have a list of items that go like this...

Item A
Item B
Item C
Item D

and I match this up to the database which has this...

Item A - 884
Item B - 879
Item B - 883
Item B - 965
Item B - 888
Item C -
Item D - 965

Okay, if you are asking if I want to filter the results to only include those with the values of 883, 879, or 884 you would be only partially right because this is what it would return:

Item A - 884
Item B - 879
Item B - 883

Instead I want it to search for anything with no value at all or a value other than the 3 listed and return blank or if it is one of those three values return only one where it would return them in order of importance (883 then 879 then 884).

So it should return this:

Item A - 884
Item B - 883
Item C -
Item D -

Hope that makes sense and answers your question.

I also see that you posted a second response which uses some sort of code I am not familiar with. Where would that type of code go? I am really looking for something that I can just paste into the Field or Criteria part of the Query each time instead of a Macro or anything like that.

Thanks!

 

[amyxkatexx]

Sounds like you have multiple values stored as one field which means your database is not normalized. You should not be storing multiple values in one record's field. If you have multiple data it should go in another table.

Believe me, I wish I had control over that, but I don't.

So all I'm trying to do at this point is work with what I've got and try and come up with new tricks to impress management.

 

[amyxkatexx]

After looking around on these threads and other sites, I have written something that I am curious to try out but before I go use my time up on the computer that has Access to try it, I wanted to know if anyone might be able to see any huge problems with it working.

Here's what I wrote to go in the Field area:

Milestone: iif([dbo_ListKey]="883","883",iif([dbo_ListKey]="879","879",iif([dbo_ListKey]="884","884","")))

Whadaya think?