Help with IIF

[tt1611]

Hi All
I have a form that i have incorporated 3 checkboxes that represent 3 sites ie checkbox A = site A, checkbox B = site B etc. My form also has a sub form (datasheet view) that loads information on all sites by default.

What I am wanting to do if for a user to select a checkbox and for information related to that site only to be loaded in the sub form. This I have been able to do no problem. What I am struggling with is that I need a report printed by the click of a button based on this filter.

My report runs off a SQL query that should load information in the report (similar to what is in the datasheet) based on what site the customer has chosen.

My SQL query so far has the following

SELECT columnA, columnB, ColumnC
FROM table
WHERE iif((forms!myform!chkboxA = TRUE), column_name LIKE "mysite___%","")

The false part of my query works fine ie if the checkbox value is false, the report shows all sites. It wont however load the specific information I need if the checkbox is checked.

Like I said this works fine on the datasheet as I implemented the filter in VBA. However for the report, I have used the query wizard and one part of my iif statement returns no values.

I have tested the code without the iif as

SELECT columnA, columnB, ColumnC
FROM table
WHERE  column_name LIKE "mysite___%";

and this works fine which means the iif portion is failing..Please help.

Thank you

 

[John Big Booty]

Try;

SELECT columnA, columnB, ColumnC
FROM table
WHERE (((table.column_name) LIKE (IIf(forms!myform!chkboxA = TRUE,"mysite___%","*"))));

I have presumed here that if chkboxA is false you want to return all records.

 

[tt1611]

Hey John (fyi...your last name has been the joke of my office).
Made some slight mods to your statement and its all working ok. Yes an unchecked box is supposed to load all information on the report

Thanks a million for your help.

 

[namliam]

Note that: Like "*" will not find any NULL values....

If you want to do stuff like this your most likely better of useing a search form and "hand" building the SQL in code ....

Have a search on forum for "Search Form" that will return some usefull samples if you search in the sample part.

 

[John Big Booty]

Hey John (fyi...your last name has been the joke of my office).
Made some slight mods to your statement and its all working ok. Yes an unchecked box is supposed to load all information on the report

Thanks a million for your help.

Glad to have helped, both with the problem at hand, and with office morale