IIF problems with dates

[grmman]

I am trying to use an IIF in a query.
I have a form where the use enters in date ranges.
date1 and date2

I want to do a check in the query to see if the first date was null and if so run the query without any date range in the criteria.
the qry worked find before I add in the date ranger.

SELECT PCN.Status_flag, PCN.purpose, PCN.Approval_date, PCN.[PCN_#], PCN.job_title, Cost_Center.location, PCN.cost_center, PCN.Comments, PCN.Hiring_Mgr, PCN.created_date, PCN.Replaced, PCN.Salary_range, PCN.Executive_approved_date, PCN.Reapproved_Date, PCN.Job_description, PCN.Position_Requirements, PCN.position_type, PCN.compensation_type, PCN.emp_name, PCN.Filled_by, PCN.How_filled, PCN.user_name, PCN.start_date, PCN.Expiration_Date, PCN.Status_memo
FROM PCN INNER JOIN Cost_Center ON PCN.cost_center = Cost_Center.number
WHERE (((PCN.Status_flag)=[forms]![frm_rpt_type]![cbo_status].[value]) AND ((PCN.purpose)=[forms]![frm_rpt_type]![cbo_purpose].[value]) AND ((PCN.Approval_date)=IIf(Not (PCN.Approval_date)=IsNull([forms]![frm_rpt_type]![date1].[value]),(PCN.Approval_date) Between [forms]![frm_rpt_type]![date1].[value] And [forms]![frm_rpt_type]![date2].[value]," ")));

 

[Golom]

Your problem is probably this
[tt]
AND PCN.Approval_date =
IIf(Not PCN.Approval_date = IsNull(X1),
(PCN.Approval_date) Between X1 And X2,
" ")
[/tt]
where I have replaced

[tt][forms]![frm_rpt_type]![date1].[value] with X1 and
[forms]![frm_rpt_type]![date2].[value] with X2[/tt]

for readability.

First, the condition in the IIF says
[tt]
Not PCN.Approval_date = IsNull(X1)
[/tt]
IsNull returns TRUE(-1) or FALSE(0) and you are testing that a date is (or is not) equal to that. Almost certainly it is not. A date of -1 is illegal and a date of 0 is Dec 31, 1899. I suspect that you just want NOT IsNull(X1) as the condition.

The second problem is that your condition ... again condensed ... looks like this
[tt]
fld = IIF ( <Condition>,
fld BETWEEN X1 AND X2,
" " )
[/tt]
If "<Condition>" evaluates to TRUE then this further reduces to
[tt]
fld = fld BETWEEN X1 AND X2
[/tt]
Which is probably illegal syntax and even if it isn't you are testing that fld is TRUE or FALSE ... again not what you want.

From your stated objective I think that you want
[tt]
IIf(Not IsNull([forms]![frm_rpt_type]![date1].[value]),

PCN.Approval_date Between [forms]![frm_rpt_type]![date1].[value]
And [forms]![frm_rpt_type]![date2].[value],

TRUE)
[/tt]
This returns the result of the BETWEEN Test if [forms]![frm_rpt_type]![date1].[value] is not NULL and returns TRUE if that field is NULL.

 

[grmman]

I put this code in
If I enter dates I get the right records back.
But if I leave the dates blank I dont get any records back.

SELECT PCN.Status_flag, PCN.purpose, PCN.Approval_date, PCN.[PCN_#], PCN.job_title, Cost_Center.location, PCN.cost_center, PCN.Comments, PCN.Hiring_Mgr, PCN.created_date, PCN.Replaced, PCN.Salary_range, PCN.Executive_approved_date, PCN.Reapproved_Date, PCN.Job_description, PCN.Position_Requirements, PCN.position_type, PCN.compensation_type, PCN.emp_name, PCN.Filled_by, PCN.How_filled, PCN.user_name, PCN.start_date, PCN.Expiration_Date, PCN.Status_memo
FROM PCN INNER JOIN Cost_Center ON PCN.cost_center = Cost_Center.number
WHERE (((PCN.Status_flag)=[forms]![frm_rpt_type]![cbo_status].[value]) AND ((PCN.purpose)=[forms]![frm_rpt_type]![cbo_purpose].[value]) AND ((PCN.Approval_date)=IIf(Not (PCN.Approval_date)=IsNull([forms]![frm_rpt_type]![txt_date1].[value]),[pcn].[approval_date] Between [forms]![frm_rpt_type]![txt_date1].[value] And [forms]![frm_rpt_type]![txt_date2].[value],True)));