Querying a Blank Field

[gtlifter]

Hi,
I'm new to access ive done a ton of searching but it seems I cant find what im looking for, im creating a filter form so users can choose multiple filters for a query, I'm trying to figure out if theres a function to leave it blank or "show all" and return all the results. I thought of an if then
IIf([Forms]![Master List Review Report Filter]![Text24]="","",[Forms]![Master List Review Report Filter]![Text24]")

but if the feild is blank it returns a blank query.

anyhelp?

 

[JamesMcS]

Welcome to the forum!

The if true part of your statement should be, I think, either "*" or "Like '*'"

 

[gtlifter]

Welcome to the forum!

The if true part of your statement should be, I think, either "*" or "Like '*'"

Hmmm that didnt work, do i need to change the feild type from text?

 

[Guus2005]

When the field is "blank", in fact it is null. Use the Nz function

select * from table1 where field1 = IIf(Len(Nz([Forms]![Master List Review Report Filter]![Text24],""))=0,[field1],[Forms]![Master List Review Report Filter]![Text24]")

You might want to add/delete some paranthesis...

The Nz function returns the default "" when [Forms]![Master List Review Report Filter]![Text24] is NULL or "". That's because we're checking for a length = 0. So both NULL and the empty string are covered.

When [Forms]![Master List Review Report Filter]![Text24] is NULL the default "" is used resulting in a [field1] = [field1] where clause. This returns all records.

Share & Enjoy!

 

[gtlifter]

When the field is "blank", in fact it is null. Use the Nz function

select * from table1 where field1 = IIf(Len(Nz([Forms]![Master List Review Report Filter]![Text24],""))=0,[field1],[Forms]![Master List Review Report Filter]![Text24]")

You might want to add/delete some paranthesis...

The Nz function returns the default "" when [Forms]![Master List Review Report Filter]![Text24] is NULL or "". That's because we're checking for a length = 0. So both NULL and the empty string are covered.

When [Forms]![Master List Review Report Filter]![Text24] is NULL the default "" is used resulting in a [field1] = [field1] where clause. This returns all records.

Share & Enjoy!

this is what i put in:
select * from [Master List] where [ASSIGNED TO] = IIf(Len(Nz([Forms]![Master List Review Report Filter]![Text24],""))=0,[assigned to],[Forms]![Master List Review Report Filter]![Text24]")

it returns a error that says "check the subquarys syntax and enclose the subquary in parenthases"

 

[Guus2005]

remove the last double quote.

...ort Filter]![Text24][COLOR="Red"]"[/COLOR])

THT

 

[JamesMcS]

Well spotted....!