Expression Builder IIF Issue (1 Viewer)

[mattcdse]

Hi all,

Should you write an expression the criteria field in a query and use IIF, an interesting little bug appears that I have not managed to get around. Should you want the expression to return a range as the criteria (i.e. Between ... And ... ) it can't do it because IIF tries to evaluate the range before it passes the result back to the query. I need it to actually pass the Between And to the query to use as it's criteria. It can't do this, so it passes back a null and of course the query fails to produce any meaningful result.

I need to do this as I have two date text boxes in a form so that people can search through specific dates, but in the table these date fields can be blank. (Basically it's a Date Completed field, so if the job hasn't been completed, the date is left as Null) So i need users to be able to leave both text boxes blank so that the resulting query will display the Null values as well. If they leave one blank, it auto sets itself to the a minimum/maximum date. When both boxes are blank i need to return nothing so the criteria is effectively blank or = Is Null And Is Not Null to display all records.

Example of WIP

IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True And IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,#15/01/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True,Between #01/01/2007# And #01/04/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,Between [Forms]![frmListImgChng]![txtDateComp2] And #31/12/2030#,Between [Forms]![frmListImgChng]![txtDateComp1] And [Forms]![frmListImgChng]![txtDateComp2])))

The red bits of code are test values. The first one passes back to the query as it is not a range, the second does not as it is.

Cheers,

Matt

 

[llkhoutx]

,#15/01/2007#,

is not a valid result because of the # signs. I would try

,format("15/01/2007","mm/dd,yyyy"),

The Between dates should probably be structured the same way.

Between [Forms]![frmListImgChng]![txtDateComp2] And #31/12/2030#

is just flat wrong.

Your IIF statement is not constructed properly; it should be of the form

IIF (EXPRESSION1, RESULT1, _
IIF (EXPRESSION2, RESULT2, _
IIF (EXPRESSION3,RESULT3, _
ELSE)))

If you build it in a Word or Texas processor, as opposed to Access, on multiple lines, you will immediately see possible errors.

 

[mattcdse]

Thanks llkhoutx,

But I'm somewhat confused.

If you put a date into and Access criteria field, it automatically surrounds it with #'s to signify that the data is a date. So i guess I need them.

As far as I'm aware I'm not using PHP becasue this is Microsoft Access which i thought was SQL and VBA.

Many people don't seem to know this, if your Windows Regional and Language settings are set to United Kingdom, all date formats are automatically read dd/mm/yyyy as that is our format and like it's been since the start of time. Believe me, I've doing it for years.

So, so much to just flat wrong!

I do agree though, the access Expression Builder is a dog of a development environment.

Cheers,

Matt

 

[boblarson]

Many people don't seem to know this, if your Windows Regional and Language settings are set to United Kingdom, all date formats are automatically read dd/mm/yyyy as that is our format and like it's been since the start of time.

From posts I've seen here, when you want to run a query, you need to format the dates to US format (lovely Microsoft feature there) in order for it to work correctly. You can display the data with your current regional settings, but it needs to pass it to the query in the US format.

 

[neileg]

From posts I've seen here, when you want to run a query, you need to format the dates to US format (lovely Microsoft feature there) in order for it to work correctly. You can display the data with your current regional settings, but it needs to pass it to the query in the US format.

Mostly, Access can cope with dates in any format in saved querydefs. If you build a query in SQL, either in the SQL view or as a string in code, you have to use US format. Probably because SQL is a US invention!

 

[Jon K]

Hi,

Assuming the table field name is [Date Completed], you can set the criteria in a new column in the query grid like this:-
-------------------------------------
Field: IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1]) And IsNull([Forms]![frmListImgChng]![txtDateComp2]), IsNull([Date Completed]),
IIf(Not IsNull([Forms]![frmListImgChng]![txtDateComp1]) And Not IsNull([Forms]![frmListImgChng]![txtDateComp2]), [Date Completed] Between [Forms]![frmListImgChng]![txtDateComp1] and [Forms]![frmListImgChng]![txtDateComp2],
[Date Completed] >=[Forms]![frmListImgChng]![txtDateComp1] or [Date Completed] <=[Forms]![frmListImgChng]![txtDateComp2]))

Show: uncheck

Criteria: True
-------------------------------------
The True in the Criteria row tells Access to treat the expression in the Field row as the criteria.

This way, you can avoid the difficulty in putting an IIF expression in the Criteria row in query Design View. When you put an IIF expression in the Criteria row, Access internally places an invisible = sign in front of IIF. Certainly, your IIF already contains all the necessary operators and doesn't need this extra = operator.
.

 

[mattcdse]

Thanks all,

Jon - The code has worked a treat and lead me to an answer. I had no idea you could use True in the criteria field the way you have and it just works

Here is my code for those who may want a similar solution.

IIf(

IsNull([Forms]![frmListImgChng]![txtDateComp1])=True And IsNull([Forms]![frmListImgChng]![txtDateComp2])=True

,[DateCompleted] Is Null Or [DateCompleted] Is Not Null

,IIf​

(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True​

,[DateCompleted] Between #01/01/2007# And [Forms]![frmListImgChng]![txtDateComp2]​

,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp2])=True​

,[DateCompleted] Between [Forms]![frmListImgChng]![txtDateComp1] And #31/12/2030#​

,[DateCompleted] Between [Forms]![frmListImgChng]![txtDateComp1] And [Forms]![frmListImgChng]![txtDateComp2])))​

Took a bit of juggling, but it's all good now.

Thanks very much Jon and everyone who's contributed. Oh and by the way, i have not changed my date formatting.

Cheers,

Matt