Query including combos and a checkbox (1 Viewer)

[Chumpalot]

Hi there,

I have a form with 4 combo boxes, a free text field and a checkbox. All are unbound. A user can select what sort of records they want displayed by choosing options from the combo boxes. This I had working fine, until I came to add the checkbox.

I have since added a field called 'Closed' to the table. I'd like the query to pull records based on whether the checkbox is ticked or not. So:

Unchecked = Retrieve all records
Checked (default) = Retrieve only those records where the field (Closed) is unchecked.

Here is a list of the controls I have on the form:

cbo_casetype
cbo_legalaid
cbo_risk
cbo_cmp
txt_lawyer

I have tried opening the query in design view and entering various WHEREs and IIFs but I cannot for the life of me get it working. I have a feeling I need to add it into the SQL with the AND operator but the SQL has become so huge I have no idea where to start.

Very grateful for any help you can offer. This is driving me nuts.

Thanks very much
David

 

[arnelgp]

you add to your query:

"... And [closed] = " & iif(Me!chk_box, "false", "[closed]")

 

[Chumpalot]

you add to your query:

"... And [closed] = " & iif(Me!chk_box, "false", "[closed]")

Thank you for your reply. I am currently trying to rebuild the mess that was the SQL code from the original query. I have this so far, which works:

SELECT Cases.Case_Type, Cases.Funding, Cases.Litigation_RAG_risk_rating, Cases.CMP, Cases.FCO_lawyer
FROM Cases
WHERE (((Case_Type)=[Forms]![frm_casefiltersedit]![cbo_casetype] Or IsNull([Forms]![frm_casefiltersedit]![cbo_casetype]))
AND ((Funding)=[Forms]![frm_casefiltersedit]![cbo_legalaid] Or (IsNull([Forms]![frm_casefiltersedit]![cbo_legalaid]))
));

When I try to add another AND statement I get a syntax error. So I go from the above, to this:

SELECT Cases.Case_Type, Cases.Funding, Cases.Litigation_RAG_risk_rating, Cases.CMP, Cases.FCO_lawyer
FROM Cases
WHERE (((Case_Type)=[Forms]![frm_casefiltersedit]![cbo_casetype] Or IsNull([Forms]![frm_casefiltersedit]![cbo_casetype]))
AND ((Funding)=[Forms]![frm_casefiltersedit]![cbo_legalaid] Or (IsNull([Forms]![frm_casefiltersedit]![cbo_legalaid]))
AND ((Risk)=[Forms]![frm_casefiltersedit]![cbo_risk] Or (IsNull([Forms]![frm_casefiltersedit]![cbo_risk]))
));

Where am I going wrong here?

Thanks again.
David

 

[plog]

AND ((Risk)=[Forms]![frm_casefiltersedit]![cbo_risk] Or (IsNull([Forms]![frm_casefiltersedit]![cbo_risk]))

You added 4 left parenthesis-- (, but only 3 right parenthesis--). That's the syntax error. No idea where you need to add that other right parenthesis, but that's the issue.

My advice is to get rid of as many as you can--just makes finding issues like this easier. "(Risk)" has no need for parenthesis and at least 1 in the begginning and end of the entire WHERE statement are extraneous as well. I think Microsoft gets paid by the character somehow so Access is always adding ones in that you don't need. Makes reading the SQL it produces horrible.

 

[Chumpalot]

OK, I've built in the various comboboxes and now have this:

SELECT Cases.Case_Type, Cases.Funding, Cases.Litigation_RAG_risk_rating, Cases.CMP, Cases.FCO_lawyer
FROM Cases
WHERE (((Forms.frm_casefiltersedit.cbo_casetype Is Null)
OR ([Case_Type]=Forms.frm_casefiltersedit.cbo_casetype))
AND ((Forms.frm_casefiltersedit.cbo_legalaid Is Null)
OR ([Funding]=Forms.frm_casefiltersedit.cbo_legalaid))
AND ((Forms.frm_casefiltersedit.cbo_risk Is Null)
OR ([Litigation_RAG_risk_rating]=Forms.frm_casefiltersedit.cbo_risk))
AND ((Forms.frm_casefiltersedit.cbo_cmp Is Null)
OR ([CMP]=Forms.frm_casefiltersedit.cbo_cmp))
);

I now just need to add in the free text field (lawyer) and the checkbox....

 

[arnelgp]

SELECT Cases.Case_Type, Cases.Funding, Cases.Litigation_RAG_risk_rating, Cases.CMP, Cases.FCO_lawyer
FROM Cases
WHERE [Case_Type]=iif(Trim([Forms]![frm_casefiltersedit]![cbo_casetype] & "")="", [case_type], [Forms]![frm_casefiltersedit]![cbo_casetype])
AND [Funding]=IIF(TRIM([Forms]![frm_casefiltersedit]![cbo_legalaid] & "")="", [Funding], [Forms]![frm_casefiltersedit]![cbo_legalaid])
AND [Risk]=iif(Trim([Forms]![frm_casefiltersedit]![cbo_risk] & "")="", [Risk], [Forms]![frm_casefiltersedit]![cbo_risk])

 

[Chumpalot]

SELECT Cases.Case_Type, Cases.Funding, Cases.Litigation_RAG_risk_rating, Cases.CMP, Cases.FCO_lawyer
FROM Cases
WHERE [Case_Type]=iif(Trim([Forms]![frm_casefiltersedit]![cbo_casetype] & "")="", [case_type], [Forms]![frm_casefiltersedit]![cbo_casetype])
AND [Funding]=IIF(TRIM([Forms]![frm_casefiltersedit]![cbo_legalaid] & "")="", [Funding], [Forms]![frm_casefiltersedit]![cbo_legalaid])
AND [Risk]=iif(Trim([Forms]![frm_casefiltersedit]![cbo_risk] & "")="", [Risk], [Forms]![frm_casefiltersedit]![cbo_risk])

Thanks. Any idea on how to build in the free text field (using LIKE I guess?). I have tried this but to no avail:

....AND [FCO_lawyer] Like "*" & [Forms]![frm_casefiltersedit]![txt_lawyer] & "*"
OR [Forms]![frm_casefiltersedit]![txt_lawyer] Is Null
);

 

[arnelgp]

....AND [FCO_lawyer] Like Chr(34) & "*" & Chr[Forms]![frm_casefiltersedit]![txt_lawyer] & "*" & Chr(34)

... you don't need the OR there since, this will retrieve the records even if the controls have null value.

 

[Chumpalot]

I received a syntax error using your last code. I had a look and now have this which runs but returns no results even when the text field has something in it.

OR ([CMP]=Forms.frm_casefiltersedit.cbo_cmp))
AND [FCO_lawyer] Like Chr(34) & "*" & [Forms]![frm_casefiltersedit]![txt_lawyer] & "*" & Chr(34));

Thank you for your help with this.

 

[arnelgp]

[FCO_lawyer] Like "'*" & [Forms]![frm_casefiltersedit]![txt_lawyer] & "*'"

 

[Chumpalot]

I almost have everything working. The only thing missing is the checkbox value.

What I would like is this:

- when the checkbox is checked then records where [Closed] is unchecked are returned
- when the checkbox is unchecked for ALL records to be returned

Any ideas with this? I have tried adding the following to my query SQL:

IIf([Forms]![frm_casefiltersedit]![chk_closed]=True,0,[Cases].[Closed])

This works for the first criteria above but not the second.

Thanks again
David