Jun 18, 2012 I have fields with null values and for those that aren't want I want to evaluate if the Cost is greater than the value and if yes, show the Cost and if not show the value. The results I get are weird. The code I am using is below. Can someone better with IIF and isnull statements review. Switch([VBR60%@List] Is Null And.
I have written a report by form. The form basically asks for a start date, end date and a technician number. These values are passed to the underlying query in a report. I want to be able to run the report for either a single technician number, or leave the field null (in the form) and run for all technicians. When I place this statement in the criteria in the underlying query: IIf(Forms!WO Labor Report Form!MID Is Null,Null,Forms!WO Labor Report Form!MID) The report runs fine as long as there is a value in the form field. If I null out the form field, no values are selected. I then tried this statement: IIf(Forms!WO Labor Report Form!MID Is Null,' 0',Forms!WO Labor Report Form!MID) I get the following error when the field in the form is null: This expression is typed incorrectly, or it is too complex to be evaluated.
For Example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. How do I write the Iif statement to run for all values when Null? Your help is appreciated, Bruce. The basis making a parameter optional is that in the query's WHERE clause each parameter is tested in this way: WHERE (SomeColumn = OR IS NULL) AND (SomeOtherColumn = OR IS NULL) AND etc The above uses equality operations, but the operations can be of any type.
Each OR operation is enclosed in parentheses to force it to evaluate independently. These parenthesized expressions are then tacked together with AND operations. The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL), for every row. By virtue of the AND operations the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned. Note that when you do this parameters should only be declared if they are of DateTime data type.
![If not null access If not null access](/uploads/1/2/5/4/125408156/893818830.jpg)
If other types were declared they could never be Null. DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates. When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view. This applies to the initial saving of the query, and if any subsequent amendments are made. If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open. It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.
So applying this to your scenario a query would be along these lines: PARAMETERS Forms!WO Labor Report Form!txtStartDate DATETIME, Forms!WO Labor Report Form!txtEndDate DATETIME; SELECT. FROM YourTable WHERE (MID = Forms!WO Labor Report Form!MID OR Forms!WO Labor Report Form!MID IS NULL) AND (YourDateField = Forms!WO Labor Report Form!txtStartDate OR Forms!WO Labor Report Form!txtStartDate IS NULL) AND (YourDateField.