CODEWITHSUNDEEP
ms-access-2007
bsilmani
bsilmani

Reputation: 11

ACCESS QUERY / CONDITIONAL

IIF(IsNull(now(),now()-1,now())

When I put this function isnull function is not working. Blank data appeared.

Upvotes: 1

Views: 457

Answers (2)

David-W-Fenton
David-W-Fenton

Reputation: 23067

IsNull() in Access is not the same as IsNull() in SQL Server. In the latter, it's a way of returning a different value if a field is found Null, as in IsNull([Field1], [Field2]) returns Field2 if Field1 is Null.

In Access, IsNull() is a Boolean function that has one argument and is used to determine if the expression passed to it evaluates to Null.

For the equivalent of the SQL Server IsNull() you can use Nz() within Access, but it's not available from outside Access (via ODBC or OLEDB). In that case, you have to convert it to use the Immediate If function, IIf(), and return the values by testing them with IsNull:

  • SQL Server: IsNull([Field1], [Field2])
  • Access Null-To-Zero: Nz([Field1], [Field2])
  • Access Immediate If: IIf(IsNull([Field1]), [Field2], [Field1])

Upvotes: 2

Christian Specht
Christian Specht

Reputation: 36451

First of all, there is one closing bracket missing:

IIF(IsNull(now()),now()-1,now())
(didn't use code tags here so I can make the brackets bold)

With the parenthesis added, this returns always the current date (without subtracting 1), because Now() can never be null.

Upvotes: 2

Related Questions