Referencing Unbound Option Group in Query

[Lori]

I have a form I'm using to collect data to search a table. One field is
called "DateComp" data type of Date. I don't want to use actual dates to
search by, simply provide an Option Group with "Completed", "Open" (value of
2) or "All". The Option Group "Status_Grp" is unbound since the data type of
the underlying field is different.

In the query the criteria for the DateComp I've tried:

IIf([Forms]![1_Main]![Status_Grp]=2,"Is Not Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"Is Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"*")

For testing purposes I'm trying to match just one of the fields, if I can do
that, nesting will be easy – hopefully I'm running out of hair.

I've tried with and without quotes, tried adding Like in front. All results
yield zero records. This is the only criteria in the query so far. Am I not
referencing the group correctly?

~Lori

 

[Klatuu]

The IIf is not the problem. Post the SQL, please

 

[GoodGirl]

the date in the table will never equal the string "Is Not Null"

more later...

 

[Lori]

I'm trying to find fields that are either, Null, Not Null or All fields.

the date in the table will never equal the string "Is Not Null"

more later...

I have a form I'm using to collect data to search a table. One field is
called "DateComp" data type of Date. I don't want to use actual dates to
search by, simply provide an Option Group with "Completed", "Open" (value of
2) or "All". The Option Group "Status_Grp" is unbound since the data type of
the underlying field is different.

In the query the criteria for the DateComp I've tried:

IIf([Forms]![1_Main]![Status_Grp]=2,"Is Not Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"Is Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"*")

For testing purposes I'm trying to match just one of the fields, if I can do
that, nesting will be easy – hopefully I'm running out of hair.

I've tried with and without quotes, tried adding Like in front. All results
yield zero records. This is the only criteria in the query so far. Am I not
referencing the group correctly?

~Lori

 

[Lori]

I stripped out all but two fields:

SELECT DISTINCTROW t_Requests.DateMade, t_Requests.DateComp
FROM t_Requests
WHERE (((t_Requests.DateComp)=IIf([Forms]![1_Main]![Status_Grp]=2,"*")));

The IIf is not the problem. Post the SQL, please

I have a form I'm using to collect data to search a table. One field is
called "DateComp" data type of Date. I don't want to use actual dates to
search by, simply provide an Option Group with "Completed", "Open" (value of
2) or "All". The Option Group "Status_Grp" is unbound since the data type of
the underlying field is different.

In the query the criteria for the DateComp I've tried:

IIf([Forms]![1_Main]![Status_Grp]=2,"Is Not Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"Is Null")
IIf([Forms]![1_Main]![Status_Grp]=2,"*")

For testing purposes I'm trying to match just one of the fields, if I can do
that, nesting will be easy – hopefully I'm running out of hair.

I've tried with and without quotes, tried adding Like in front. All results
yield zero records. This is the only criteria in the query so far. Am I not
referencing the group correctly?

~Lori

 

[Lori]

I'm going to slink away and hide in shame now.

My default for the group was set to an Closed, value 1, and I was looking
for 2.

~Lori