My criteria doesn't work.

[Lydia]

Hi,

What is wrong with both of following criteria:

This one, Access tells me "The expression is to complex..."
iif(forms!frmProduct!cmbPL="Totals", Is Null , like forms!frmProduct!cmbPL &
"*")

This one doesn't return any value,

iif(forms!frmProduct!cmbPL="Totals", Null, like forms!frmProduct!cmbPL & "*")


Thanks

Lydia

 

[adam.vogg]

Just to clarify, you want logic setup like: if cmbPL = "Totals", then
no criteria at all, else cmbPL & "*"

if thats the case, try

iif(cmbPL = "Totals", Like "*", Like cmbPL & "*")


you will of course need your Forms!... prefix too

 

[Marshall Barton]

What is wrong with both of following criteria:

This one, Access tells me "The expression is to complex..."
iif(forms!frmProduct!cmbPL="Totals", Is Null , like forms!frmProduct!cmbPL &
"*")

This one doesn't return any value,

iif(forms!frmProduct!cmbPL="Totals", Null, like forms!frmProduct!cmbPL & "*")

The result of a function (IIf) can only be a value, not a
partial criteria expression.

Where are you using that kind of thing?

I suspect you are trying to use them as criteria in the
query design grid. If so try something more like:

Like Forms!frmProduct!cmbPL & "*" OR Forms!frmProduct!cmbPL
= "Totals"

(all on one line)

 

[Lydia]

Thanks for you reply.

When the cmbPL="Totals", I want all records whose "PL" field value Is Null
to show up, not instead of all records.

Thanks.

Lydia

 

[adam.vogg]

maybe this?

iif(cmbPL = "Totals", "", Like cmbPL & "*")

 

[Lydia]

The criteria is in a query and for the field named PL. User select from a
drop down box on a form called frmProduct the value PL. If a user selects
"Totals" from this drop down box list, I want it to return all values whose
PL value is null. This is kind of unusual, however it is what I need to
accomplish right now.

Thanks.

Lydia

 

[John Spencer]

The where statement in the query would look something like the following.

WHERE PL like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" AND PL
is Null)

In Query Design View, I think that would be entered as
Field: PL
Criteria: Like forms!frmProduct!cmbPL OR (forms!frmProduct!cmbPL="Total" and
Is Null)

Access will restructure that when you save the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Lydia]

Yes! It worked.

Thanks.

Lydia