Test nulls in query 2

[Moss100]

Hello I have run across an old problem - that of nulls and empty fields.

I want to return a yes no rersult in a query so I can quickly check if sort-codes are missing in my table.

I have entered: TEST RESULT: IsNull([Sort Code])

Thus gives the same result whether or not a value exists.

Can someone give the the correct expression to use within a query.

I have tried aslo using iff and switch functions but not getting there.

I guess ideally I would like to be able to display the words "Correct" when an entry exists and "Error" when the field is blank.

Many thanks Mark

 

[dhookom]

If I understand, you want a query column to display when a field is either null or blank.

Test Result: IIf(Len([Sort Code]&"")= 0, "Correct","Blank")

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I believe is the other way around:

Test Result: IIf(Len([Sort Code] & "" ) = 0, "Blank or Null", "Has Value")

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

@Andy, good catch. I'm falling victim to multi-tasking and advancing age


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

As always, thank you very much and a very happy new year you both.

Regards Mark.

 

[Andrzejek]

Don't you think Duane deserves a * ?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

@Mark Happy New Year to you also.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016