You cannot compare a field to Null. Instead, test if it Is Null.
Additionally, Access tends to misunderstand calculated fields, so typecast
the result with CVDate(), i.e.:
CVDate(IIf([Field1] Is Null, Null, IIf([Field1] < [Field2], [Field1],
Null)))
More on the issue with Nulls:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
More on the need to typecast:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Spook" <st**************@sainsburys.co.uk> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I am tring to write build a query where the outcome of a result depends
on one date being older than the other.
example
Field Name: IIf([Field Name 1]=Null,Null,IIf([Field Name 1]<[Field Name
2],[Field Name 1],Null))
However the result comes up as an "#Error". What am I doing wrong or
can you not do queries like this with dates.
Stephen