Check boxes, query and form

[RSDAnalyst]

Hello all,
This is my first ever post on this website and I hope someone can help with my Access problem.

I have table called Campaigns that has a column called Status. This Status column has 4 possible entries: Proposed, Approved, Cancelled and Closed. I am building a query linked to an Access form. On the Access form I have 4 CHECK BOXES relating to the 4 possible entries under the Status column. A user may wish to see more than 1 Status entry and therefore may tick more than 1 check box. I.e. tick the Proposed check box along with the Approved check box. How do I get the Access query to reflect multiple check box combinations BUT under a single column split by 4 check boxes? So far I can get the query to work when 1 check box is ticked. (I’m writing the query via SQL view)

(((Campaigns.Status)="Approved") = forms!Form1!Approved=-1 OR forms!Form1!Approved IS NULL)

Any help would be much appreciated.

 

[apr pillai]

Check boxes can only store either TRUE (-1) or FALSE (0) value. i.e. only one of these values at a time. You may use four check box fields on the table to show more than one status (if selected) to show on the form.

Alternatively use a numeric field with different status codes like:

1 - Proposed
2 - Approved
3 - Cancelled
4 - Closed

Use a Combobox to provide these values to insert into the Status field. Let the Combobox's first column width = 0.

 

[spikepl]

You are using a multivalued field, something nobody apart from Microsoft and people venturing into databases for the first time are happy about.

But in any case, make up similar criteria for each of the other cases. Each criterion you write on a new line in the query design grid, in the same column or colums as the first.

Each new lineof criteria functions as a big fat OR

In SQL view, wrap the old criterion in parantheses , and add an OR before each new set wrapped in parantheses.

 

[nanscombe]

You are using a multivalued field, something nobody apart from Microsoft and people venturing into databases for the first time are happy about.

Not if only one value, from the set of four, is used at a time.

RSDAnalyst:

Assuming Form is called form1 then you could try

SELECT Campaigns.*
FROM Campaigns
WHERE (Campaigns.Status)=IIf([Forms]![Form1]![Proposed]=True,"Proposed") OR (Campaigns.Status)=IIf([Forms]![Form1]![Approved]=True,"Approved") OR (Campaigns.Status)=IIf([Forms]![Form1]![Cancelled]=True,"Cancelled") OR (Campaigns.Status)=IIf([Forms]![Form1]![Closed]=True,"Closed");

Basically there are four criteria under the field Campaigns.Satus:

IIf([Forms]![Form1]![Proposed]=True,"Proposed")
IIf([Forms]![Form1]![Approved]=True,"Approved")
IIf([Forms]![Form1]![Cancelled]=True,"Cancelled")
IIf([Forms]![Form1]![Closed]=True,"Closed")

If the appropriate CheckBox is checked then look for the text value in the field.

 

[spikepl]

#3 The basic evils of multivalued fields do not go away, just because one value is used in some cases: D

 

[nanscombe]

How do you figure it's a multivalued field?

It sounds more like a plain old status field which holds the current status of a project to me.

The form just runs a query which brings back records which match the one, or more, selected statuses.

 

[spikepl]

Darned you are right !!!!!!!!!!!! I need to polish my specs.

I think I confused myself by OP's text, who seemingly wishes to have the output of 4 checkboxes into one field ("single column split into 4 checkboxes").

This is doable by doing some bitmasking, but a pain in the butt and fun only for incarnated geeks. Otherwise, each checkbox needs its own field, so in this instance, four boolean fields in the record.

Otherwise you are right - to see DIFFERENT records with the desired value of status indicated by checkboxes, yours is the right recipe.

 

[RSDAnalyst]

Sorry for delay in responding back to all your individual comments and useful suggestions. I just got caught with other things. I was meaning to respond sooner, my apologies. Nigel (nanscombe) was probably closest to what I finally tried myself in the interim whilst waiting for a response to my post.

Below is what I finally came up and seems to work:
(((Campaigns.Status)="Proposed") = forms!Form1!Proposed=-1 OR forms!Form1!Proposed IS NULL OR
((Campaigns.Status)="Approved") = forms!Form1!Approved=-1 OR forms!Form1!Approved IS NULL OR
((Campaigns.Status)="Closed") = forms!Form1!Closed=-1 OR forms!Form1!Closed IS NULL OR
((Campaigns.Status)="Cancelled") = forms!Form1!Cancelled=-1 OR forms!Form1!Cancelled IS NULL);

Again thanks to everyone to replied to my post.

 

[RSDAnalyst]

Hello, folks. I thought I had this cracked! Nigel (nanscombe) code works a treate. If I use the campaigns.suggestion iff above on its own (nigel's) it works great.

Combining with the existing SQL statement it does not function. Any clues anyone?

Again any assitance would be much appreciated.

This what I have so far:

WHERE (Costs.Supplier = forms!Form1!Supplier OR forms!Form1!Supplier IS NULL) and
(Campaigns.Owner = forms!Form1!Owner OR forms!Form1!Owner IS NULL) and
(Costs.Period = forms!Form1![Cost Period] OR forms!Form1![Cost Period] IS NULL) and
((Campaigns.Status)=IIf([Forms]![Form1]![Proposed]=True,"Proposed") OR
(Campaigns.Status)=IIf([Forms]![Form1]![Approved]=True,"Approved") OR
(Campaigns.Status)=IIf([Forms]![Form1]![Cancelled]=True,"Cancelled") OR
(Campaigns.Status)=IIf([Forms]![Form1]![Closed]=True,"Closed"));

Here is the issue. If I remove the Campaigns.status IIF section, the drop down on Cost.Supplier, Campaigns.Owner and Cost.Period works fine. Then if I remove these and keep the IIf section, they work fine. Combined together like above, it falls down? Something is missing to bind the two groups together to work together.

Any clues any anyone?

Many thanks.

 

[nanscombe]

Ok, let's get a bit closer to the goal.

I don't know what your Cost.Period looks like so ...

WHERE
Campaigns.Owner Like "*" & [Forms]![Form1]![Owner] & "*" AND
Cost.Supplier Like "*" & [Forms]![Form1]![Supplier] & "*" AND 
(
Campaigns.Status=IIf([Forms]![Form1]![Proposed]=True,"Proposed") Or
Campaigns.Status=IIf([Forms]![Form1]![Approved]=True,"Approved") Or
Campaigns.Status=IIf([Forms]![Form1]![Cancelled]=True,"Cancelled") Or
Campaigns.Status=IIf([Forms]![Form1]![Closed]=True,"Closed")
);

Campaigns.Owner Like "*" & [Forms]![Form1]![Owner] & "*"

Campaigns.Owner would return "**", ie everything, if the textbox is empty or "*Owner Name*", everything with the string "Owner Name" in it, if an Owner Name is entered. A similar thing happens with Cost.Supplier.

 

[RSDAnalyst]

Hi nanscombe (Nigel). Thanks for trying to help with my Access issue. I tried your suggestion. It worked partly. I.e. If I choose any or all of the campaign.status radio buttons. Worked fine. Combined at the same time with Supplier worked fine. But then If I was clear options start from say Supplier, I got no records back?
My boss suggested another idea via creating a module to handle the multiple options under the single column of Campagin status. We found something on another website. Now all is good.

Final code for anyone else who is interested:
WHERE (Costs.Supplier = forms!Form1!Supplier OR forms!Form1!Supplier IS NULL) and
(Campaigns.Owner = forms!Form1!Owner OR forms!Form1!Owner IS NULL) and
(Costs.Period = forms!Form1![Cost Period] OR forms!Form1![Cost Period] IS NULL) and
(IsSelectedVar("Form1","List49",[Campaigns.Status])=-1);

The IsSelectedVar relates to the module.

Again many thanks Nigel for your help.

 

[nanscombe]

Sorry I couldn't get you any closer using a query.

Anyway, here is a novelty that does the same sort of thing but based on a Split Form.

Being able to use VBA code to build the SQL string makes life a bit easier but it works mainly for Forms and Reports.

You can't (or at least I can't) use this to make a Queries so easily.That said, you could build a temporary Query, like CampaignQuery_Export, when required.

Have a peek and I hope it may give you some ideas for the future.