IIf statement - Criteria in query.

[grande]

I have many different samples, of five different types (Chicken, Turkey, Human, Ont. Swint, Sask. Swine). For all except Sask Swine, I want the criteria for the "Species" column to be "C. coli". If it's Sask Swine, I don't want there to be a criteria.

This is what I have:
IIf(GetCurrentSampleTypeAsString()<>"Sample_SwineSask","C. Coli","*")
I have also tried
IIf(GetCurrentSampleTypeAsString()<>"Sample_SwineSask","C. Coli")
and
IIf(GetCurrentSampleTypeAsString()<>"Sample_SwineSask","C. Coli",Null)
and
IIf(GetCurrentSampleTypeAsString()<>"Sample_SwineSask","C. Coli","")

If I remove the criteria, I get all the proper results. If I put in one of the above-mentioned IIf statements, I get no results.

Note: For Swine Sask, the Species column is always empty.

-------------------------
Just call me Captain Awesome.

 

[Crowley16]

you should be using the last of the lines you gave...

check that you are getting the right results with GetCurrentSampleTypeAsString() and that the names are exactly what they should be...

--------------------
Procrastinate Now!

 

[traingamer]

Is the rest of your statement
where field =iif(...
or
where field like iif(...
?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[grande]

Hi all,

Crowley16 --> It's returning the correct value, but it's still not working.

traingamer --> It was =, I changed it to Like, it still doesn't work.

Thanks for the ideas, but do you have any other suggestions?

-------------------------
Just call me Captain Awesome.

 

[PHV]

Sample_SwineSask" is not the same as "Sask. Swine" ...
Anyway you may consider something like this:
WHERE ([your tested field]='C. Coli' OR GetCurrentSampleTypeAsString()='Sample_SwineSask')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

are you saying there's more?

if you're using the <> test then it should be returning "C. Coli" for all values not exactly equal to "Sample_SwineSask", and "" when they are...

--------------------
Procrastinate Now!

 

[grande]

Okay, let me rephrase my question.

I want the query to assume that all samples of the type 'Sample_SwineSask' have a Species of C. Coli. For any other sample type (Sample_Chicken, Sample_Turkey, etc), I want the query to select ONLY the samples of that sample type, and have a species of C. Coli.

Sample Data:

Type              Species
Sample_Chicken    C. Coli
Sample_Chicken    C. Laria
Sample_Chicken    C. Jejuni
Sample_SwineSask
Sample_SwineSask
Sample_SwineSask

If the current sample type is Chicken (GetCurrentSampleTypeAsString='Sample_Chicken'), the query should only display the first record. If the current sample type is Saskatoon Swine (GetCurrentSampleTypeAsString='Sample_SaskSwine'), the query would return all three Samples that have the type 'Sample_SaskSwine'.

-------------------------
Just call me Captain Awesome.

 

[PHV]

Something like this ?
WHERE Trim([Species] & '') = IIf(GetCurrentSampleTypeAsString='Sample_SwineSask', '', 'C. Coli')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

That gives me a syntax error. I think it has to do with the & '' in the Trim() statement.

-------------------------
Just call me Captain Awesome.

 

[grande]

Is there perhaps a Wildcard feature in queries/SQL?

-------------------------
Just call me Captain Awesome.

 

[PHV]

Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Sure...

SELECT CampyTbl_Grid_Results.Submission AS [Sample Tracking Number], CampyTbl_Grid_Results.TestDate AS [Test Date], CampyTbl_Grid_Results.[01_ SSM-Migration] AS [SSM Migration], CampyTbl_Grid_Results.[02_ BEIA] AS BEIA, CampyTbl_Grid_Results.[04_ Wet Mount] AS [Wet Mount], CampyTbl_Grid_Results.[05_ Hippurate Hydrolysis] AS [Hippurate Hydrolysis], CampyTbl_Grid_Results.[06_ PCR] AS PCR, CampyTbl_Grid_Results.[08_ H2S] AS H2S, CampyTbl_Grid_Results.[10_ Nalidixic Acid Disk] AS [Nalidixic Acid Disk], CampyTbl_Grid_Results.[09_ Cephalothin] AS Cephalothin, CampyTbl_Grid_Results.[12_ Campylobacter Species], IIf(GetCurrentSampleTypeAsString()="Sample_SwineSask","Sask","Not Sask") AS [Text]
FROM CampyTbl_Grid_Results
WHERE (((CampyTbl_Grid_Results.Submission) Like GetCurrentSampleIdentCriteria()) AND ((CampyTbl_Grid_Results.[12_ Campylobacter Species])=IIf(GetCurrentSampleTypeAsString()="Sample_SwineSask","","C. Coli")));

-------------------------
Just call me Captain Awesome.

 

[PHV]

And this ?
SELECT Submission AS [Sample Tracking Number], TestDate AS [Test Date], [01_ SSM-Migration] AS [SSM Migration], [02_ BEIA] AS BEIA, [04_ Wet Mount] AS [Wet Mount], [05_ Hippurate Hydrolysis] AS [Hippurate Hydrolysis], [06_ PCR] AS PCR, [08_ H2S] AS H2S, [10_ Nalidixic Acid Disk] AS [Nalidixic Acid Disk], [09_ Cephalothin] AS Cephalothin, [12_ Campylobacter Species], IIf(GetCurrentSampleTypeAsString()="Sample_SwineSask","Sask","Not Sask") AS [Text]
FROM CampyTbl_Grid_Results
WHERE Submission Like GetCurrentSampleIdentCriteria() AND Trim([12_ Campylobacter Species] & "")=IIf(GetCurrentSampleTypeAsString()="Sample_SwineSask","","C. Coli");

You really have 2 different UDF (GetCurrentSampleIdentCriteria and GetCurrentSampleTypeAsString) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

:-O It works! Huzzah!

There's actually more than 2 UDF (User defined function?) that do essentially the same thing. There's another one to get it's "friendly name," and another still to get the number.

I didn't design it. I'm just updating it to work with Saskatoon Swine.

-------------------------
Just call me Captain Awesome.