The NZ() function seems not to work when called from VB, is there an alternative-CodePudding

I am working on an add-query.
In the add query I am trying to fill field C with with a true or false value, depending on the value in field A.
If the value in field A equals -1 the value in field C should be true (-1)

I thought that the solution would be something like the following, but I am getting #Error in the results:

C: IIf([A]='-1',True,False)

A solution that seems to return the desired outcome is the following:
B: IIf(Nz([A])='-1',True,False)

The problem with this (NZ-function) is that it throws an error when running the query with VB (ADO,DAO or OLEDB)

My question is:
What formula can be used to get to the desired results without using the NZ-function The desired results are as given in field B

CodePudding user response：

Since you're using quotes, I assume your value is a string. In that case, Nz will convert a Null to a zero-length string.

You can achieve the same by simply concatenating an empty string:

IIf([A] & ''='-1',True,False)

A more general solution is to use IIf, which allows you to specify an alternative value on nulls:

IIf(IIf([A] IS NULL, '', [A]) = '-1', True, False)

CodePudding user response：

As [A] is numeric, all you need is to compare it with True:

C: [A]=True