using an IFF function to return NULL

[Guest_imported]

I'm trying to use a access query to search a data table for the value 0 and for Null fields if the queryselect-field in the queryselect-table has the value -1.
when I write the criteria like this:

0 or IIF( [queryselct]![queryselect] = -1,NULL,0)

I get the error massage:
You have entered an operand without an operator.
and then marks the NULL statement.
Can someone please tell me what I'm doing wrong because MS Help can't.

Asgard

 

[LittleSmudge]

It's not the IIf statement

0 Or Null will die on you regardless of how you generate the Null.

Get the IIf to return something more useful.



'ope-that-'elps.

G LS

 

[AsgardvdS]

but it works fine if I use
0 or NULL
as a criteria for my query only if I try to get it out of the IIF funktion it does not work.

 

[LittleSmudge]

0 Or Null will return Null - so what are you actually doing with it?


By
[queryselct]![queryselect] = -1

do you mean
[queryselct-table]![queryselect-field] = -1
???


You truth table seems to be

Value of 
[queryselct]![queryselect]     Output
           -1                   Null
         anything else           0


Is that what you're really after?
Why do you WANT to return Null ?


I'm sorry if I'm missing something obvious here - but what you're saying seems so alien to me.


G LS

 

[AsgardvdS]

what I want:
If the field queryselct in the table queryselct is -1 the returned value shall be NULL else the returned value shall be = (or somethin else doesn't matter) I only need the NULL.

the queryselect-field is a switch the user uses to define if he wants the query to look for table fields that are NULL.

This is beacuse I have a triple selct switch that generates: TRUE(-1), FALSE(0) and NULL entries in a specific field.

the user is supposed to be able to select if he only wants to search for FALSE entries or if he wants to search for FALSE AND NULL entries.

 

[jebry]

Hi!

Add a field to your query:

CriteriaField: IIF( [queryselct]![queryselect] = -1,NULL,0)

then in the criteria of the field in question use:

=CriteriaField

hth
Jeff Bridgham
bridgham@purdue.edu

 

[AsgardvdS]

thanks
but that also didn't work.

I still get the same error, maybe my system is messed up, I don't know I think I'll have to figure out a different way to do it.

Asgard

 

[jebry]

Hi!

In thinking again, my suggestion will not work that way. One way you can do this using two queries:

query1

Select PrimaryKey, IIF( [queryselct]![queryselect] = -1,NULL,0) As CriteriaField From YourTable

query2

Select YourTable!* From YourTable Inner Join query1 On YourTable.PrimaryKey = query1.PrimaryKey Where YourTable!YourField = query1!CriteriaField

hth
Jeff Bridgham
bridgham@purdue.edu