query criteria problem

[ndltx5]

I'm tring to add IIF(expression,return1,return2) function to the criteria box in my query. The return1 and return2 expressions returns the results i would expect when they are alone but when I try putting them together(expression below) in the IFF() nothing is returned(I don't get an error message). Could you tell me why this doesn't return any results?

Thank you


IIf([Forms]![frm_Release_Search]![INIT_START]=DateAdd("m",-1,"01-Feb-90") And [Forms]![frm_Release_Search]![INIT_END]=Date(), ([tbl_Release].[INIT_DATE]) Between [Forms]![frm_Release_Search]![INIT_START] And [Forms]![frm_Release_Search]![INIT_END] Or ([tbl_Release].[INIT_DATE]) Is Null,([tbl_Release].[INIT_DATE]) Between [Forms]![frm_Release_Search]![INIT_START] And [Forms]![frm_Release_Search]![INIT_END])

 

[traingamer]

What are you actually trying to do here?
Your IIF looks like it will return a boolean (true / false) answer. You are not getting an error message because it looks like valid syntax.

What kind of field is this criteria for? (date, numeric, boolean?)

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

 

[ndltx5]

Criteria is for a date. I have a form that has a query box and i'm trying to add a search by date range to the form. Each record in the database has an initiate date but in some of the records the INIT date is blank. So in order to show all the records when the from is first brought up, i set the defaults for the start date of the range = 1/1/1990 and the end date of the range = date(). Then in the criteria in the query i put:

Between [Forms]![frm_Release_Search]![INIT_START] And [Forms]![frm_Release_Search]![INIT_END] Or Is Null

So this shows all the records in the data base but when i do a search by date range I no longer want the records with blank dates to appear, so i thought i could use the IIF() as i did above. So that if the START and END date on the range is equal to the default value it shows the records with a NULL in the date and when the START and END date isn't equal to the default it won't show the records with a blank INIT date. Hope that makes sense

 

[PHV]

Perhaps this (I don't like the query grid, so I'm not sure):
Between [Forms]![frm_Release_Search]![INIT_START] And [Forms]![frm_Release_Search]![INIT_END] Or (Is Null AND [Forms]![frm_Release_Search]![INIT_START]=#1990-01-01# AND [Forms]![frm_Release_Search]![INIT_END]=Date())

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ndltx5]

Thank you that worked