iff Statment to show one or many records

[Jai_Friday]

Morning All,

I am sure this is a simple query but I cant get my head around it,

I have an access query that checks 1 check box and a field on a form which
relate to a customer database,

The check box if ticks - shows ALL customer records
If not ticked it will show one customer record which is held in the field

the syntax i have tried is as follows

IIF([Forms]![Diary]![All customers check]=0,[Forms]![Diary]![Customer
Field],Not Null)

so if the check box is unticked filter by the customer field otherwise show
anything which is not null

This half works , when not ticked it shows me an individual customer , when
ticked it shows nothing,

In the else clause I have also tried >" " , Not "0" and still nothing....

However I tested the else clause by passing a valid customer code in it and
it returns data?

Any idea

Thanks in Advance

Jai

 

[John Spencer]

Try entering this as criteria under the Customer Field
[Forms]![Diary]![All customers check]<>0 OR =[Forms]![Diary]![Customer Field]

Access should reformat that if you are using the design view.

It will add a calculated field
Field: [Forms]![Diary]![All customers check]
Criteria (Line 1): <> 0
Criteria (Line 2): Blank

And set up Customer field like
Field: Customer
Criteria (Line 1): Blank
Criteria (Line 2): [Forms]![Diary]![All customers check]


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

 

[Jai_Friday]

Thank you John

Thats has worked perfectly

Regards

Jai

Try entering this as criteria under the Customer Field
[Forms]![Diary]![All customers check]<>0 OR =[Forms]![Diary]![Customer Field]

Access should reformat that if you are using the design view.

It will add a calculated field
Field: [Forms]![Diary]![All customers check]
Criteria (Line 1): <> 0
Criteria (Line 2): Blank

And set up Customer field like
Field: Customer
Criteria (Line 1): Blank
Criteria (Line 2): [Forms]![Diary]![All customers check]


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

Morning All,

I am sure this is a simple query but I cant get my head around it,

I have an access query that checks 1 check box and a field on a form which
relate to a customer database,

The check box if ticks - shows ALL customer records
If not ticked it will show one customer record which is held in the field

the syntax i have tried is as follows

IIF([Forms]![Diary]![All customers check]=0,[Forms]![Diary]![Customer
Field],Not Null)

so if the check box is unticked filter by the customer field otherwise show
anything which is not null

This half works , when not ticked it shows me an individual customer , when
ticked it shows nothing,

In the else clause I have also tried >" " , Not "0" and still nothing....

However I tested the else clause by passing a valid customer code in it and
it returns data?

Any idea

Thanks in Advance

Jai