Query Criteria Help Please SQL (1 Viewer)

R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
I am creating a query for several fields of data. My current criteria is as follows: ([Forms]![Formname]![labelname]). So this criteria goes on for several fields of information and in the end I linked this to a form which will display information in a query search based on the selections on the form by pressing a button. Each field has a yes/no combo box. Now the problem is that when I use AND to separate each criteria in sql, it displays information only for those that values are yes in only those fields. For example, If I am looking for certain names of parks and search for parks with biking lanes and free parking with a yes value and put a no value for basketball courts because that is not what I am looking for, then the query shows only parks with those two yes value and assumes that I am looking for parks without basketball courts when in fact I DO NOT want the no value to have an impact on the result. I want the query to display information based on the yes values with the no value having no impact on the result. I feel like I'm missing something very basic here so please help. If any clarification is needed then please let me know! Also using OR to separate each field does not work because it takes into account the no values and ends up displaying ALL the results.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
Hi. I think what you're looking for (and probably a better approach anyway) is to use a dynamic filter rather than a fixed filter. For example, your current setup checks all columns for a match. But in your scenario, you don't want to consider all the criteria. So, what you want is to only check those specific criteria rather than all of them. There are some demos on how to do it. I'll see if I can find them for you.
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
Thank you so much! That is exactly what I am looking for. Please let me know if you know how to achieve this.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
Thank you so much! That is exactly what I am looking for. Please let me know if you know how to achieve this.
Click to expand...
Hi. Have a look here and let us know if you don't find anything you can use, and we'll try again.
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
Hi, that is not exactly what I am looking for. I am looking to find a way to put in sql language so that I can have no effect from the no answers and search only based on the yes answers. Do you know how I can achieve this?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
Hi, that is not exactly what I am looking for. I am looking to find a way to put in sql language so that I can have no effect from the no answers and search only based on the yes answers. Do you know how I can achieve this?
Click to expand...
Hi. Yes, I do, and those demos I linked you to should also show you how to do it. Which one did you try? I can't download any of them right now. Let me see if I can find some posted sample code instead. Hang on...
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
I will look through that, thank you so much! In the meantime, I wanted to know if you could help me directly?
The following is my code:
SELECT [Special Notes Matrix (Main)].[Name Of Special Note]
FROM [Special Notes Matrix (Main)]
WHERE ((([Special Notes Matrix (Main)].[Paving/Resurfacing])=([Forms]![THEFORMTOUSE]![Paving/Resurfacing]![yes])) AND (([Special Notes Matrix (Main)].[Drainage Work])=([Forms]![THEFORMTOUSE]![Drainage Work])) AND (([Special Notes Matrix (Main)].[Bridge Work])=([Forms]![THEFORMTOUSE]![Bridge Work])) AND (([Special Notes Matrix (Main)].[Sign Structures])=([Forms]![THEFORMTOUSE]![Sign Structures])) AND (([Special Notes Matrix (Main)].[Traffic Signal])=([Forms]![THEFORMTOUSE]![Traffic Signal])) AND (([Special Notes Matrix (Main)].[Pavement Repairs])=([Forms]![THEFORMTOUSE]![Pavement Repairs])) AND (([Special Notes Matrix (Main)].[Tree Removal])=([Forms]![THEFORMTOUSE]![Tree Removal])) AND (([Special Notes Matrix (Main)].[Landscape Work])=([Forms]![THEFORMTOUSE]![Landscape Work])) AND (([Special Notes Matrix (Main)].[Work In Waterways])=([Forms]![THEFORMTOUSE]![Work In Waterways])) AND (([Special Notes Matrix (Main)].[Guiderail Work])=([Forms]![THEFORMTOUSE]![Guiderail Work])) AND (([Special Notes Matrix (Main)].Excavation)=([Forms]![THEFORMTOUSE]![Excavation])) AND (([Special Notes Matrix (Main)].[Work On Parkway/Expressway])=([Forms]![THEFORMTOUSE]![Work On Parkway/Expressway])) AND (([Special Notes Matrix (Main)].[Recharge Basin Work])=([Forms]![THEFORMTOUSE]![Recharge Basin Work])) AND (([Special Notes Matrix (Main)].[Full Roadway Closure/Detours])=([Forms]![THEFORMTOUSE]![Full Roadway Closure/Detours])) AND (([Special Notes Matrix (Main)].[Work On Reservations])=([Forms]![THEFORMTOUSE]![Work On Reservations])) AND (([Special Notes Matrix (Main)].[Right Of Way Acquisition])=([Forms]![THEFORMTOUSE]![Right Of Way Acquisition])) AND (([Special Notes Matrix (Main)].[Utility Work])=([Forms]![THEFORMTOUSE]![Utility Work])) AND (([Special Notes Matrix (Main)].[Sheathing & Shoring])=([Forms]![THEFORMTOUSE]![Sheathing & Shoring])) AND (([Special Notes Matrix (Main)].[All Projects])=([Forms]![THEFORMTOUSE]![All Projects])));

What am I doing wrong?
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
SELECT [Special Notes Matrix (Main)].[Name Of Special Note]
FROM [Special Notes Matrix (Main)]
WHERE ((([Special Notes Matrix (Main)].[Paving/Resurfacing])=([Forms]![THEFORMTOUSE]![Paving/Resurfacing])) AND (([Special Notes Matrix (Main)].[Drainage Work])=([Forms]![THEFORMTOUSE]![Drainage Work])) AND (([Special Notes Matrix (Main)].[Bridge Work])=([Forms]![THEFORMTOUSE]![Bridge Work])) AND (([Special Notes Matrix (Main)].[Sign Structures])=([Forms]![THEFORMTOUSE]![Sign Structures])) AND (([Special Notes Matrix (Main)].[Traffic Signal])=([Forms]![THEFORMTOUSE]![Traffic Signal])) AND (([Special Notes Matrix (Main)].[Pavement Repairs])=([Forms]![THEFORMTOUSE]![Pavement Repairs])) AND (([Special Notes Matrix (Main)].[Tree Removal])=([Forms]![THEFORMTOUSE]![Tree Removal])) AND (([Special Notes Matrix (Main)].[Landscape Work])=([Forms]![THEFORMTOUSE]![Landscape Work])) AND (([Special Notes Matrix (Main)].[Work In Waterways])=([Forms]![THEFORMTOUSE]![Work In Waterways])) AND (([Special Notes Matrix (Main)].[Guiderail Work])=([Forms]![THEFORMTOUSE]![Guiderail Work])) AND (([Special Notes Matrix (Main)].Excavation)=([Forms]![THEFORMTOUSE]![Excavation])) AND (([Special Notes Matrix (Main)].[Work On Parkway/Expressway])=([Forms]![THEFORMTOUSE]![Work On Parkway/Expressway])) AND (([Special Notes Matrix (Main)].[Recharge Basin Work])=([Forms]![THEFORMTOUSE]![Recharge Basin Work])) AND (([Special Notes Matrix (Main)].[Full Roadway Closure/Detours])=([Forms]![THEFORMTOUSE]![Full Roadway Closure/Detours])) AND (([Special Notes Matrix (Main)].[Work On Reservations])=([Forms]![THEFORMTOUSE]![Work On Reservations])) AND (([Special Notes Matrix (Main)].[Right Of Way Acquisition])=([Forms]![THEFORMTOUSE]![Right Of Way Acquisition])) AND (([Special Notes Matrix (Main)].[Utility Work])=([Forms]![THEFORMTOUSE]![Utility Work])) AND (([Special Notes Matrix (Main)].[Sheathing & Shoring])=([Forms]![THEFORMTOUSE]![Sheathing & Shoring])) AND (([Special Notes Matrix (Main)].[All Projects])=([Forms]![THEFORMTOUSE]![All Projects])));

Sorry this is the updated code, very slight difference
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
I will look through that, thank you so much! In the meantime, I wanted to know if you could help me directly?
The following is my code:
SELECT [Special Notes Matrix (Main)].[Name Of Special Note]
FROM [Special Notes Matrix (Main)]
WHERE ((([Special Notes Matrix (Main)].[Paving/Resurfacing])=([Forms]![THEFORMTOUSE]![Paving/Resurfacing]![yes])) AND (([Special Notes Matrix (Main)].[Drainage Work])=([Forms]![THEFORMTOUSE]![Drainage Work])) AND (([Special Notes Matrix (Main)].[Bridge Work])=([Forms]![THEFORMTOUSE]![Bridge Work])) AND (([Special Notes Matrix (Main)].[Sign Structures])=([Forms]![THEFORMTOUSE]![Sign Structures])) AND (([Special Notes Matrix (Main)].[Traffic Signal])=([Forms]![THEFORMTOUSE]![Traffic Signal])) AND (([Special Notes Matrix (Main)].[Pavement Repairs])=([Forms]![THEFORMTOUSE]![Pavement Repairs])) AND (([Special Notes Matrix (Main)].[Tree Removal])=([Forms]![THEFORMTOUSE]![Tree Removal])) AND (([Special Notes Matrix (Main)].[Landscape Work])=([Forms]![THEFORMTOUSE]![Landscape Work])) AND (([Special Notes Matrix (Main)].[Work In Waterways])=([Forms]![THEFORMTOUSE]![Work In Waterways])) AND (([Special Notes Matrix (Main)].[Guiderail Work])=([Forms]![THEFORMTOUSE]![Guiderail Work])) AND (([Special Notes Matrix (Main)].Excavation)=([Forms]![THEFORMTOUSE]![Excavation])) AND (([Special Notes Matrix (Main)].[Work On Parkway/Expressway])=([Forms]![THEFORMTOUSE]![Work On Parkway/Expressway])) AND (([Special Notes Matrix (Main)].[Recharge Basin Work])=([Forms]![THEFORMTOUSE]![Recharge Basin Work])) AND (([Special Notes Matrix (Main)].[Full Roadway Closure/Detours])=([Forms]![THEFORMTOUSE]![Full Roadway Closure/Detours])) AND (([Special Notes Matrix (Main)].[Work On Reservations])=([Forms]![THEFORMTOUSE]![Work On Reservations])) AND (([Special Notes Matrix (Main)].[Right Of Way Acquisition])=([Forms]![THEFORMTOUSE]![Right Of Way Acquisition])) AND (([Special Notes Matrix (Main)].[Utility Work])=([Forms]![THEFORMTOUSE]![Utility Work])) AND (([Special Notes Matrix (Main)].[Sheathing & Shoring])=([Forms]![THEFORMTOUSE]![Sheathing & Shoring])) AND (([Special Notes Matrix (Main)].[All Projects])=([Forms]![THEFORMTOUSE]![All Projects])));

What am I doing wrong?
Click to expand...
Oh, I'm sorry, I misunderstood your last question. I was saying rather than have a fixed criteria in your SQL statement. Leave the criteria out and only apply a dynamic criteria using VBA. You could have a somewhat dynamic query with fixed criteria if you weren't dealing with Yes/No fields. But since you are, then it would be best to use VBA instead.
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
How should I implement this? Since I am looking at yes/no fields, the search forms are not very helpful in my case. Or maybe I am looking at the wrong links?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
How should I implement this? Since I am looking at yes/no fields, the search forms are not very helpful in my case. Or maybe I am looking at the wrong links?
Click to expand...
Did you look at the second link I posted earlier. It showed a sample code snippet of what you could try. For example, let's say you have three checkboxes and you only want to consider the first two. As it is right now, your SQL's WHERE clause includes all three. If you use VBA, you can reduce it to only have two, or how ever many criteria was selected from your form.

Perhaps, the next best thing is if you could post a sample copy of your db, and maybe someone here can modify it for you.
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
I am not using checkboxes, instead it is a lookup wizard with 2 options; yes and no. So there are dropdown combo boxes. Don't know if this makes a difference but let me know if it does. I will try and post the database as well so it is easier and clearer to understand. Thanks again for your help! I am fairly new at navigating Access
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
Here is part of the database. The form and Query are both in capital letters. Please let me know if you are able to help with the coding or have alternative suggestions. Thank You!
 

Attachments

  • SAMPLE.accdb
    652 KB · Views: 99
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
I am not using checkboxes, instead it is a lookup wizard with 2 options; yes and no. So there are dropdown combo boxes. Don't know if this makes a difference but let me know if it does. I will try and post the database as well so it is easier and clearer to understand. Thanks again for your help! I am fairly new at navigating Access
Click to expand...
Ah yes, lookup fields add an extra layer of complications and issues. Have a look here to see why.
 
R

rsingh4377

Registered User.
Local time
Today, 01:37
Joined
Jun 17, 2019
Messages
39
Should I change the entire format of my database so far? Or is there any way of achieving what I am looking to do with a particular code? Please look at my database and let me know! thank you!
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:37
Joined
Oct 29, 2018
Messages
21,454
rsingh4377 said:
Should I change the entire format of my database so far? Or is there any way of achieving what I am looking to do with a particular code? Please look at my database and let me know! thank you!
Click to expand...
Hi. I will, as soon as I get a chance. I can't download files at the moment (using phone).
 
You must log in or register to reply here.

Similar threads

T
Solved Access criteria
Replies
7
Views
148
teemax
T
N
Can't get query criteria to read a form text field
Replies
9
Views
427
Pat Hartman
P
Uncle Gizmo
Plastic PIG
Replies
1
Views
109
ColinEssex
ColinEssex
S
Criteria for Query Question
Replies
5
Views
357
Pat Hartman
P
M
Querying for idle projects. Yes/No field to autopopulate a text field with a set string, "no new update".
Replies
13
Views
264
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom