MS Access Question: how to use function to locate Blank record

HIROYUKI TAMURA
HIROYUKI TAMURA used Ask the Experts™
on
I would like to use following function to locate Blank record
Blank: IIf(InStr([table],"")<>0,"Blank")
However it locates all not null record as "Blank"
Please advise.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
What constitutes a blank record?  A record where all the fields are NULL?  You could easily create a query for this, no?

I think you will need to explain your table structure and data more in detail.

Also, why not setup your table structure so you don't have blank records?

Author

Commented:
Thank you, Daniel.
Blank record means Null and "" record.
Table has a field in short text, contains Null and ""
I want to create a query if the record is Null or "", then return "Blank" string
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That could be:

Blank: IIf(Nz([YourFieldName]) = "","Blank", Null)

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
If Len(YourField) =0

Open in new window

should cover your cases
Distinguished Expert 2017

Commented:
I would suggest

Blank: IIf(Nz([YourFieldName],"") = "","Blank", Null)

rather than

Blank: IIf(Nz([YourFieldName]) = "","Blank", Null)

Because Nz() works differently in VBA than it does in SQL as well as differentiating between string and non-string data types.   I'm assuming that your field is a text data type otherwise it makes no sense at all to return "blank".

In this particular case, assuming you are working with a text data type, omitting the substitution value and allowing it to default will end up OK but most people have never read the help entry for Nz() and don't understand that the default may not be what they expect in certain situations so I recommend always specifying what result you want returned.  Rely on defaults at your own peril.  The really scary part of this particular default is that the function works differently in VBA than in SQL.  By always providing the result value, I avoid having to remember to consider the  what environment the expression will be evaluated by and does that impact the result I expect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial