Asatoma Sadgamaya
asked on
Avoid counting blank values when apply Access formula on text data type
Hi
I use below formula in Access query column, when I copy this column data set and paste in excel, then look at the count value, it shows full count of cells in the column including blank cells. How can I modify this formula which keeps only non blank values as it should be and keep blank values as blank.
EvNum1: IIf(Left(Trim([EveNum]),2) ="KK",[Eve Num],"")
Hope you get my point.
Cheers
I use below formula in Access query column, when I copy this column data set and paste in excel, then look at the count value, it shows full count of cells in the column including blank cells. How can I modify this formula which keeps only non blank values as it should be and keep blank values as blank.
EvNum1: IIf(Left(Trim([EveNum]),2)
Hope you get my point.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TRIM() function blows up if you try to trim a Null. The & "" both forces the value in the field to implicitly convert to a string AND converts a null to an empty string. You aren't the first one to have problems of this sort trying to get counts.
Dale's technique is one I highly encourage you to understand and get use to using - much more accurate.
Dale's technique is one I highly encourage you to understand and get use to using - much more accurate.
ASKER
Thanks Dale
happy to help.
ASKER
what is that & "" in the formula does?
Cheers