IIF in queries

[ScubaStevo]

Hi

I am trying to run a query that uses an IIF clause like this:

IIf([Forms]![Form Name]![cboDate]="Before",<[Forms]![Form Name]![DateBefore],>[Forms]![Form Name]![DateAfter])

It is meant to select all records where the date is less than [DateBefore] when [cboDate] = "Before" or greater than [DateAfter] when [cboDate] = "After" but instead it returns zero records.

How can I fix this?

Thanks, Steve

 

[PHV]

WHERE ((yourDateField<[Forms]![Form Name]![DateBefore] AND [Forms]![Form Name]![cboDate]='Before')
OR (yourDateField>[Forms]![Form Name]![DateAfter] AND [Forms]![Form Name]![cboDate]='After'))



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScubaStevo]

Thanks for that, but I get an error mesasge when trying to run it:
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.

Any ideas how to fix that?

 

[PHV]

This is SQL code to b inserted at the right place in the SQL view pane of the Query window, not in a criteria cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ScubaStevo]

At the right place? I inserted it in the SQL view

 

[ScubaStevo]

It doesn't seem to like the blank fields, eg, if [cboDate] = "Before" then the field [DateAfter] is blank and vice versa.

 

[PHV]

Provided DateAfter or DateBefore are null instead of blank:
WHERE ((yourDateField<Nz([Forms]![Form Name]![DateBefore],0) AND [Forms]![Form Name]![cboDate]='Before')
OR (yourDateField>Nz([Forms]![Form Name]![DateAfter],0) AND [Forms]![Form Name]![cboDate]='After'))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886