iff Statment to show one or many records

  • Thread starter Thread starter Jai_Friday
  • Start date Start date
J

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
 
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
 
Thank you John

Thats has worked perfectly

Regards

Jai

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top