Help with SQL query with checkboxes (1 Viewer)

[Jaybee214]

Hi All!
I have Access 2007-2010. I'm a beginner to SQL so I think this is probably an easy solve for someone other than me! Any help as soon as possible is greatly appreciated.
My table is called ASU CA
My field names for the query are Region (data type is Text in the table), Brokerage – non NRI (checkbox-data type is yes/no- format is true/false in the table), and Brokerage – NRI (data type is yes/no- format is true/false in the table)
I'm trying to have the query generate when Region is "East" and the Brokerage – non NRI and Brokerage – NRI are both True (check boxes are selected).
The below is my SQL but it just comes up blank, I was thinking this might just be too much for Access to sort? Or it's probably just more complicated than I thought.

SELECT [ASU CA].Region, [ASU CA].[Brokerage - non NRI], [ASU CA].[NRI Brokerage]
FROM [ASU CA]
WHERE ((([ASU CA].Region)="East") AND (([ASU CA].[Brokerage - non NRI])=True) AND (([ASU CA].[NRI Brokerage])=True));

This is for someone at work and I suggested just making two queries one for East-Brokerage-non-NRI and one for East-Brokerage- NRI but this person would like it all one one query... Thanks in advance for any help!

 

[Ranman256]

you can make it in 1 query, but the form must analyse the check box options.
the form can show ALL records, and by clicking check boxes, it alters the filter

if  chkBox1.value  then   sWhere = sWhere & " and [Field1]=true"
if  chkBox2.value  then   sWhere = sWhere & " and [Field2]=true"
    'remove 1st And
sWhere= mid(sWhere,4)

  'just use the filter
me.filter = sWhere
me.filterOn = true

 

[plog]

Can you provide some example data to demonstrate your issue? Provide 2 sets of data:

A. Starting data from ASU_CA. Include field names and enough data to cover all cases.

B. Expected results from A. Show what you hope your query will produce when you feed it the data you provide in A.

 

[Jaybee214]

Hi plog-Here are some examples, I hope this helps, I tried to attach a screen shot but I kept getting an error that the file was to big to attach.

A: Query shows 3 headings: Region, NRI, and Non NRI
Region:data could include 4 options for criteria-West,Central,East, or GI CM
NRI: shows a check box, checkbox is either selected or not selected
non NRI: shows a check box, checkbox is either selected or not selected

B: I need the query to only show me when the Region is East and the NRI and non NRI checkboxes are selected.

 

[plog]

Nope. Sample data.

Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 13, 2/1/2008
Sally, 44, 3/9/2010
Tim, 3, 7/6/2009

 

[Jaybee214]

ok, thanks for your patience-first time posting on the forum. Hopefully this is what you mean. I also was able to attach a file this time-I copied the query from access to Excel to show sample data. I just need the query to only show when region is east, nonNri and NRI are both TRUE.

AsuCa
Region, nonNri, NRI
East,TRUE, FALSE
East,FALSE, FALSE
East, FALSE, TRUE
East, TRUE, FALSE
West, FALSE, FALSE
Central, FALSE, FALSE
GI CM, TRUE, FALSE

 

[plog]

I'm lost. You provided 2 sets of data but I'm not certain you provided me with what I asked for.

Is the file the starting data (A) and the data you typed the expected results (B)? If so, the expected results don't seem to match your written description.

So, one more try:

Can you provide some example data to demonstrate your issue? Provide 2 sets of data:

A. Starting data from ASU_CA. Include field names and enough data to cover all cases.

B. Expected results from A. Show what you hope your query will produce when you feed it the data you provide in A.

 

[Jaybee214]

Apologies, that last post was starting data- A. Here is the expected results-B attachment. Hoping I can solve with a query of SQL. Only want to see East Region when NRI Brokerage or Brokerage - non NRI are TRUE.

 

[plog]

Only want to see East Region when NRI Brokerage or Brokerage - non NRI are TRUE.

That's fundamentally different from what you initially asked for:

I'm trying to have the query generate when Region is "East" and the Brokerage – non NRI and Brokerage – NRI are both True

The key difference in those descriptions is one uses 'and' the other 'or'. In SQL those are the exact words you use in queries and you were using the wrong one. This is the SQL you want:

SELECT [ASU CA].Region, [ASU CA].[Brokerage - non NRI], [ASU CA].[NRI Brokerage]
FROM [ASU CA]
WHERE (Region="East") AND (([NRI Brokerage]=True) OR ([Brokerage - non NRI]=True))

 

[Jaybee214]

Ah I see! sorry for the confusion and thank you so much for your help! It works perfectly