Link to home
Start Free TrialLog in
Avatar of Asatoma Sadgamaya
Asatoma SadgamayaFlag for United Kingdom of Great Britain and Northern Ireland

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",[EveNum],"")

Hope you get my point.

Cheers
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Asatoma Sadgamaya

ASKER

Thanks for your suggestion Dale,

what is that & "" in the formula does?

Cheers
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.
Thanks Dale
happy to help.