iif statement in criteria ?

[rich.barry]

Can one set the criteria based on an iif statement, e.g.

iif([Forms]![Form1]![OptionGrp1]=0,Is Null, Not Is Null)

the idea being it looks at the value of the control and sets the criteria to Is Null or Not Is Null dependant on the control value.

I can't seem to make it work, and had to go to putting [Forms]![Form1]![OptionGrp1]=0 as an expression, putting a criteria of True, and a criteria of Is Null for the Field in the query. 2nd line (OR) equaled False , Not Is Null.

Would like to make the first idea work, as the query would be much tidier (4 OR statements vs 8)

Ta

Richard

 

[Jon K]

Try this in a column in the query grid, using the correct field name:-

Field: iif([Forms]![Form1]![OptionGrp1]=0, [FieldName] Is Null, [FieldName] Is Not Null)
Show: uncheck
Criteria: <>False


The problem with your original setting is that your criteria began with an iif() function, which was not an operator. For any criteria that did not begin with an operator, Access internally added the = operator. This made your whole expression read as:

[FieldName] = iif([Forms]![Form1]![OptionGrp1]=0, Is Null, Not Is Null)

which was syntactically incorrect as it had two operators: = and Is

 

[rich.barry]

Cunning, it works well.

Unfortunately it means to help myself I have to learn exactly what qualifies as on operator and what doesn't. Access Help falls a bit flat when I ask it this question.

Thanks

Richard

 

[Jon K]

As far as criteria are concerned, the list is quite short. A criterion in the Criteria cell usually begins with one of these operators:-

=, <, >, <=, >=, <>

IS (used with Null)

LIKE "pattern"

BETWEEN ... AND ...

IN (value1, value2, ...)

IN (subquery)

 

[tsale]

Hi I am struggling to find an answer in the forums...

I have a form where users select a site using a combo box called "cboSite", and then either tick a check box called "AllYears" to get all the data available for the site, or enter a year in a field called "txtStartYear" and a field called "txtEndYear" to get only the data betweeen those years for that site. They then click a button which runs a macro that runs a query called "qryExport" and then exports the query to an excel sheet.

I have managed to get it to work in terms of selecting the right site, but am struggling to get the year selection to work!

At first I tried putting an Iif statement in the query criteria, but have also tried it as an expression. The expression I've been using is:

IIf ( Forms![frmExport]![AllYears] = True , [tblEnvironmentalData]![Survey Date] = "*" , [tblEnvironmentalData]![Survey Date] Between ( Like "*" & Forms![frmExport]![txtStartYear]) And ( Like "*" & Forms![frmExport]![txtEndYear]) )

but the query doesnt let me do this! please help!!

 

[marlan]

Hi,

see #4 above: http://www.access-programmers.co.uk/forums/showpost.php?p=120940&postcount=4

basically, the query builder is a graphic interface for creating SQL statements, understanding SQL could help a lot.

I recommend you red some SQL tutorials:
http://www.sqlcourse.com/intro.html
http://www.w3schools.com/SQl/default.asp

or just google "sql tutorial"

All the best!

 

[tsale]

Marlan,

I have looked through the links you sent, and having tried typing it out in SQL it is still not working for me! I would really appreciate it if I could be pointed in the right direction.... This is what I have in SQL:

SELECT * FROM tblEnvironmentalData
WHERE (((tblEnvironmentalData.[Site ID])=[Forms]![frmExport].[cboSite])) AND (tblEnvironmentalData.[Survey Date] Between (Iif(Forms![frmExport]![AllYears]=True,'%', Forms![frmExport]![txtStartYear])) AND (Iif(Forms![frmExport]![AllYears]=True,'%', Forms![frmExport]![txtEndYear])))

 

[tsale]

Ok,

I have figured out where I was going wrong. The issue was to do with the form, [txtStartYear] and [txtEndYear] were not formatted to short date (the format that [Survey Date] was set to), also I changed the if [AllYears] is true value to be a numerical date value.

Here is a copy of my query in SQL format:

SELECT * FROM tblEnvironmentalData 
WHERE tblEnvironmentalData.[Site ID]=[Forms]![frmExport].[cboSite] 
AND tblEnvironmentalData.[Survey Date]Between (Iif([Forms]![frmExport].[AllYears]=True,1,[Forms]![frmExport].[txtStartYear])) AND (Iif([Forms]![frmExport].[AllYears]=True,now(),[Forms]![frmExport].[txtEndYear]))

Hope it helps anyone else who comes a cropper!

 

[namliam]

Using IIF in SQL in general, in the where clause in particular is a bad idea.

If you need custom SQL depending on form input, then use VBA to build the SQL for you.
This may work for one where clause but quickly gets out of hand and becomes overly complex.... eventually causing issues

For example your "All years" solution above will cause problems for [Survey Date] fields that contain a NULL value....
In this case this may not matter but All Years suggests you want to select all records, regardless if the field is filled or not. So in this case the complete "AND [survey date]..." should simply be omitted instead of making a messy SQL solution.

In vba it should look something like:

dim mySQL as string
mySQL = "SELECT * " & _ 
        " FROM tblEnvironmentalData  " & _ 
        " WHERE tblEnvironmentalData.[Site ID]= " & [Forms]![frmExport].[cboSite]
If [Forms]![frmExport].[AllYears] then
     currentdb.querydefs("YourQuery").sql = mySQL
else 
     currentdb.querydefs("YourQuery").sql = mySQL & _ 
                                        " AND tblEnvironmentalData.[Survey Date] Between " & [Forms]![frmExport].[txtStartYear] & " AND " & [Forms]![frmExport].[txtEndYear]
endif

I should point out that using spaces in column names is a bad habit, but atleast you are pre-fixing your objects so I wont ...

 

[Corsica]

Try this in a column in the query grid, using the correct field name:-

Field: iif([Forms]![Form1]![OptionGrp1]=0, [FieldName] Is Null, [FieldName] Is Not Null)
Show: uncheck
Criteria: <>False


This was helpful to me. Many thanks!