asked on
MS Access Question: how to use function to locate Blank record
I would like to use following function to locate Blank record
Blank: IIf(InStr([table],"")<>0,"
However it locates all not null record as "Blank"
Please advise.
Blank: IIf(InStr([table],"")<>0,"
However it locates all not null record as "Blank"
Please advise.
Microsoft Access
Last Comment
PatHartman
ASKER
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
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
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionIf Len(YourField) =0
Your help has saved me hundreds of hours of internet surfing.
fblack61
I would suggest
Blank: IIf(Nz([YourFieldName],"")
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.
Blank: IIf(Nz([YourFieldName],"")
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.
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?