DateDiff('d', date(),DateAdd('m', term, createdt)) as days
改成
DateDiff('d', date(),DateAdd('m', iif(isnull(term),0,term), iif(isnull(createdt),0,createdt))) as days
就行了,
感觉Access并没有先解释
where (createDT is not null)
and (term is not null)这样的条件,
如果先解释条件应该就不用在select字段中做空值判断了
select * from (select contract.*,
DateAdd('m', term, createdt) as enddate,
DateDiff('d', date(),DateAdd('m', iif(isnull(term),0,term), iif(isnull(createdt),0,createdt))) as days
from contract
where (createDT is not null)
and (term is not null))
where days>30
这样可以了,感觉Access解释SQL语句的顺序真是有问题
select * from (select contract.*,
DateAdd('m', term, createdt) as enddate,
DateDiff('d', date(),DateAdd('m', term, createdt)) as days
from contract
where (createDT is not null)
and (term is not null))
where days>30
这样试了还是一样的错
哦,实际上还是刚才的问题,刚才结帖太快了,搞错了,下面的语句还是有问题
select * from (select contract.*,
DateAdd('m', term, createdt) as enddate,
DateDiff('d', date(),DateAdd('m', term, createdt)) as days
from contract
where (createDT <> null)
and (term <> null))
where days>30