CODEWITHSUNDEEP
sqlvb.netms-access
MK01111000
MK01111000

Reputation: 832

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

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)

enter image description here

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

Upvotes: 0

Views: 292

Answers (2)

Gustav
Gustav

Reputation: 55961

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

C: [A]=True

Upvotes: 0

Erik A
Erik A

Reputation: 32682

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)

Upvotes: 1

Related Questions