By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Building a query with dates

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a


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

Stephen

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.