Null

[PeterS]

Hi



My query works 100% until I try to execute a "Is Not Null" criteria

This is what it looks like:

SAF_Asset	ForkliftNumber	ServiceDate	OperationHours	Record
Yes	53	2018/11/05	2382
Yes	53	2019/01/10	24539
Yes	53	2019/01/21	2461,5	2019/01/21
Yes	53	2019/02/11	2500,1
Yes	53	2019/02/18	2512,5
Yes	54	2017/02/15	6956,4
Yes	54	2017/03/17	6656

What I want to do is filter out all the blank record in the "Record" field (which is based in an IIF statement).
When I apply the Is Not Null criteria in the "Record" field then there no change.

Is this because the "Record" field is an IIf clause?
Record: IIf([ServiceDate] Between Date()-"30" And Date()-"40",[ServiceDate],"")

Please help

[ranman256]

you are using strings "30" , dates are numeric, Date()-30

or
between DateAdd("d",-30,Date) and DateAdd("d",-40,Date)

but I wouldn't use the IIF, instead make another field in the query that pulls dates in that range, then apply your criteria inside that field.

[pbaldy]

I'll point out that you formula doesn't put Null there, it puts a zero length string. You'd need to test for whatever value you have there:

http://www.baldyweb.com/NullEmptyEtc.htm

so more like

<>""

or return Null instead of "".

[PeterS]

Thank you guys