Help with a IIF statement 1

[gbraden]

From a Friend:

It seems like I’m beginning to do more programming in Access than I anticipated, but if you can help me with an IF statement, I’d appreciate it…perhaps you’ll have a better way of doing this.

Anyway, I created a field called “infection” and I want to take all the records from a field called specimen_sources, but I want infection to either say “blood” or “other”. For instance if specimen_source is null I want it to say “other”, if it says blood, I want it to say “blood”, or if it says skin or any other thing else I want it to say “other”. Does that make sense?

Here’s the code:

IIf([Specimen_Source] Like "blood", "blood") Or IIf([Specimen_Source]<>"blood", "other") Or IIf([Specimen_Source] Is Null, "other")

This retrieves only records that either say blood or other—about 272 rows where I should have over 3,000.

Any help you can provide will certainly be helpful.

Thanks in advance (for my friend)


Visit me at

http://www.aviationartist.com

 

[mp9]

If you want to use IIf, all you need is this:

IIf([Specimen_Source] Like "*blood*", "blood", "other")

http://www22.brinkster.com/accessory/

 

[gbraden]

Is using IIF statements the best way to do this?


Visit me at

http://www.aviationartist.com

 

[mp9]

IIF is almost always slow, and this problem will be compounded further since you are using the LIKE operator which is also prone to being sloooooooooow.

However it is a very easy, neat solution so unless you're finding that it's unacceptably slow...?

 

[kaatjev]

Since you only have 272 out of 3000 I am led to ask you the following question: are you placing this IIF statement in the criteria field?

If so, it needs to be placed in a new field so that you can then query on it.

Hope this helps.