Building a query with dates

[Spook]

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

[Allen Browne]

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.c o.uk> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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

[Angelo]

it sounds like the error u're getting is from a mismatched data type.
when i tried ur sample code with a text input to the [field1] field in
ur statement, it yielded the same #ERROR result u got. there's probably
a mismactched data type somewhere, text with date, or something of the
sort.

here's the code i used.
IIf(IsNull([date1]) Or [date1]>[date2],Null,[date1])

hope this helps.

[Spook]

Cheers guys for the help. I have just got back from a mmeting and will
try your suggestions tomorrow.

Stephen