Null value for Date Field 1

[DoubleWeb]

I'm trying to make this criteria to work in a query that has a datefield called "datesolved". The Criteria points to a opened form that has a group called "frame37" with 3 options. The value "1" if selected should return only the NULL values in the date field. If Value is "2" should return whatever is NOT NULL in the date field, otherwise (="3"), should return NULLS and NOT NULL dates in the "datesolved" field. Here is what I wrote:

IIf(([Forms]![CP_statd]![frame37])=1,Is Null,(IIf(([Forms]![CP_statd]![frame37])=2,Is Not Null,[CP_main].[datesolved])))

ACCESS automatically chages to:

IIf(([Forms]![CP_statd]![frame37])=1,([CP_main].[datesolved]) Is Null,(IIf(([Forms]![CP_statd]![frame37])=2,([CP_main].[datesolved]) Is Not Null,[CP_main].[datesolved])))

This is not working because it does not returning the records that the date fields are NUll.

Thanks for any help.

 

[JerryKlmns]

IIf(([Forms]![CP_statd]![frame37])=1,"Is Null",(IIf(([Forms]![CP_statd]![frame37])=2,"Is Not Null",[CP_main].[datesolved])))

 

[DoubleWeb]

Nops.. now the error says that "the expression is too complex to be evaluated or was not typed correctly..

 

[Golom]

There was just an extra "("

IIf([Forms]![CP_statd]![frame37]=1,"Is Null",
IIf([Forms]![CP_statd]![frame37]=2,"Is Not Null",
[CP_main].[datesolved]))

 

[DoubleWeb]

Still gives me the same msg "the expression is too complex to be evaluated or was not typed correctly..".
What happens is that if the option group [frame37] is selected on 3 (the value=3), therefore, according to our code, would return anything for [CP_main].[datesolved]. well, it returns only the records with dates on them and not the ones with NULL values.

If option 1 or 2 are selected, I get the error msg above... If i remove the Quotes " from the IS NULL and the Is NOT NULL, I do not get the error, but the query does not return any records at all.

 

[Golom]

Ohhh ... this is in a query and not a stand-alone IIf statement?

If so, let's see the query. The most likely problem is that the double quotes are interfering with parsing the statement and giving you that message.

 

[DoubleWeb]

You are right. is part of a query. Its a big one bit here ir is:

SELECT CP_main.caseID, CP_main.dept, CP_main.computercode, CP_main.complex, CP_main.reservation, CP_casetype.casetype, CP_main.gstLname, CP_main.user, CP_main.dateopen, CP_main.timeopen, CP_main.Immediateaction, CP_main.datesolved, CP_main.compinvolved, CP_main.compammount, CP_main.compitem, CP_main.fromwho, IIf(IsNull([datesolved]),1,0) AS opencase, IIf([compinvolved]=-1,1,0) AS compstat, IIf([immediateaction]=-1,1,0) AS iniok, [datesolved]-[dateopen] AS rangeclose
FROM CP_main INNER JOIN CP_casetype ON CP_main.casetype = CP_casetype.casetypeID
WHERE (((CP_main.dept)=[Forms]![CP_statd]![dept]) AND ((CP_main.complex)=IIf(IsNull([Forms]![CP_statd]![building]),[CP_main]![complex],[Forms]![CP_statd]![building])) AND ((CP_main.dateopen) Between [forms]![CP_statd]![startdate] And [forms]![CP_statd]![enddate]) AND ((CP_main.datesolved)=IIf([Forms]![CP_statd]![frame37]=1,"Is Null",IIf([Forms]![CP_statd]![frame37]=2,"Is Not Null",[CP_main].[datesolved]))));

 

[Golom]

Change the double quotes to single quotes. I suspect however that the result won't be what you expect.

The test will resolve to

[li]CP_main.datesolved = 'Is Null' for Frame37 = 1[/li]
[li]CP_main.datesolved = 'Is Not Null' for Frame37 = 2[/li]
[li]CP_main.datesolved = [CP_Main].[datesolved] otherwise[/li]

These statements are testing for the strings 'Is Null' or 'Is Not Null'. They are not testing for a NULL or NOT NULL condition in the result. If that is your intent then perhaps you want something like

AND 
IIf([Forms]![CP_statd]![frame37]=1, CP_main.datesolved Is Null,
IIf([Forms]![CP_statd]![frame37]=2, CP_main.datesolved Is Not Null,
1 = 1 ))

 

[DoubleWeb]

Perfect! The Code worked good Golom. I wanted the condition in the result. Thats why I posted innitially that access was changing it.
Star for you!
Question: how does the "1=1" works in the IIF statement? It will make it <>False?

 

[Golom]

1 = 1 is just short hand for "all records". It will evaluate to True for every record ... essentially no record filtering from this test. That is of course combined with the various other tests in the WHERE clause to produce a True (include this record) or False (don't include it) decision.

Oh ... and by the way ... welcome to Tek-Tips.

 

[DoubleWeb]

Got it. Thanks a LOT Golom!

 

[PHV]

Another way, without IIf construct:
AND (([Forms]![CP_statd]![frame37]=1 AND CP_main.datesolved Is Null)
OR ([Forms]![CP_statd]![frame37]=2 AND CP_main.datesolved Is Not Null)
OR [Forms]![CP_statd]![frame37]=3)

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