Formula to return a true blank

[dcwood57]

Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists.

 

[Pete_UK]

No, a formula cannot return a true blank, because the cell contains
the formula !!

Have you tried something like this:

=IF(j4="","",j4)

Hope this helps.

Pete

 

[Don Guillett]

try
=if(len(trim(j4))<1,"",j4)

 

[dcwood57]

I think that this:

is the problem. It doesn't matter what formula I put in, the criteria range
will not be blank. I've been looking for a solution for this for quite a
while. I'm afraid that the solution is not to be found without programming.
I may write a VBA script to do what I'm trying to do. Or perhaps learn more
about Access Report writing as the database that I'm querying is an Access DB
anyway.

Thanks for your help.

Don

 

[Pete_UK]

You're welcome - good luck with your macro.

Pete

 

[ilia]

Instead of ISBLANK(J4) use LEN(J4)=0.

I think that this:


is the problem. It doesn't matter what formula I put in, the criteria range
will not be blank. I've been looking for a solution for this for quite a
while. I'm afraid that the solution is not to be found without programming.
I may write a VBA script to do what I'm trying to do. Or perhaps learn more
about Access Report writing as the database that I'm querying is an Access DB
anyway.

Thanks for your help.

Don






- Show quoted text -