IIf problem...

[wh00t]

I am using IIf to make a selection box on a form optional to run a report from a query, the formula I have works fine apart from one!

for one of the fields I have 2 combo boxes, start date and end date. The formula I have been using is as follows
IIf([Forms]![form]![Combo7] Is Null,[Date],[Forms]![form]![Combo7]) - this works fine

but for the dates I still need it optional, but then if it is not null use Between..And , so I tried
IIf([Forms]![form]![Combo1] Is Null,Between [Date],[Forms]![form]![Combo1] And ],[Forms]![form]![Combo3])

but this does not work, can anyone give me any other ways to do this?

 

[Mile-O]

Not promising that it'll work, but try this:

IIf([Forms]![form]![Combo1] Is Null,[Date] Between [Forms]![form]![Combo1] And [Forms]![form]![Combo3],[Forms]![form]![Combo1])

 

[wh00t]

eek typo

I have tried
IIf([Forms]![form]![Combo1] Is Null, [Date],Between [Forms]![form]![Combo1] And ,[Forms]![form]![Combo3])

not

IIf([Forms]![form]![Combo1] Is Null,Between [Date],[Forms]![form]![Combo1] And ],[Forms]![form]![Combo3])

 

[pdx_man]

Remove the comma:

IIf([Forms]![form]![Combo1] Is Null, [Date],Between [Forms]![form]![Combo1] And [Forms]![form]![Combo3])

 

[Jon K]

Assuming the field name is Date, try this setting in a column in the query grid:-

Field: IIf([Forms]![form]![Combo1] Is Null, [Date]=[Date], [Date] Between [Forms]![form]![Combo1] And [Forms]![form]![Combo3])
Show: uncheck
Criteria: <>False


<>False is one of Access's defaults. It tells Access the expression in the Field: cell is a criterion. If you directly type the expression in the Where Clause of the SQL statement in SQL View, you don't need to type <>False.

[Date]=[Date] will return every record that has a value in the Date field. If you need to return also those records in which the Date field is null, you can use:-

Field: IIf([Forms]![form]![Combo1] Is Null, ([Date]=[Date] or IsNull([Date])), [Date] Between [Forms]![form]![Combo1] And [Forms]![form]![Combo3])


Hope it helps.