Searching Multiple Fields

[JustPunkin]

OK, I have made some good progress on this program, but I've found a flaw in my thinking.

I'm using a SQL string to call a query. In that SQL string, I'm using a lot of If/Then statements. It's working the way I want it to work. You generally have three choices Yes, No, NA. If it's Yes, it searches for -1, if it's no, it searches for 0, if it's NA, it eliminates it from the query.

This works great, until I get to a search criteria that is not Yes or No.

In my table, I have two fields NoDC1 and NoDC2. In my search form, I have a txtbox for Number of DC's. You can enter any number you want.

I then have this in my SQL:

If Me.txtDCNo = "" Then
  strSQL = strSQL & "like '*'"
ElseIf Me.txtDCNo <> "" Then
  strSQL = strSQL & "AND tblMechanicalFeatures.NoDC1 = " & Me.txtDCNo & "" & vbNewLine
  strSQL = strSQL & "OR tblMechanicalFeatures.NoDC2 = " & Me.txtDCNo & "" & vbNewLine
End If
Trim (Me.txtDCNo)

It gives me the desired results. If there is a number there, it searches the two fields and returns the results that have the number in either field.

The problem arises when I add another criteria to the mix. Because it's written as an AND/OR statement, the SQL string returns this:

SELECT tblMechanicalFeatures.*, [General Project Information].*
FROM tblMechanicalFeatures INNER JOIN [General Project Information] ON tblMechanicalFeatures.KeyNumber = [General Project Information].[Key Number]
WHERE [General Project Information].[Surrogate Rings] = -1
AND tblMechanicalFeatures.NoDC1 = 10
OR tblMechanicalFeatures.NoDC2 = 10
ORDER BY tblMechanicalFeatures.KeyNumber;

It is searching for all projects where surrogate rings = -1 and NoDC1 is 10 OR all projects where NoDC2 is 10.

What I want it to do is search for all projects where surrogate rings = -1 and NoDC1 or NoDC2 = 10.

Any advice?

 

[Thomko]

WHERE clauses are evaluated left to right. That's why the AND is evaluated first and the result then ORed with the third condition.
You can enforce an order of evaluation with parenthesis like this (I think that is what you want):

WHERE [General Project Information].[Surrogate Rings] = -1 AND
(tblMechanicalFeatures.NoDC1 = 10 OR tblMechanicalFeatures.NoDC2 = 10)

HTH
Thomas

 

[JustPunkin]

WHERE clauses are evaluated left to right. That's why the AND is evaluated first and the result then ORed with the third condition.
You can enforce an order of evaluation with parenthesis like this (I think that is what you want):

WHERE [General Project Information].[Surrogate Rings] = -1 AND
(tblMechanicalFeatures.NoDC1 = 10 OR tblMechanicalFeatures.NoDC2 = 10)

HTH
Thomas

Thank you. I'll give this a try.

~Brenda

 

[JustPunkin]

WHERE clauses are evaluated left to right. That's why the AND is evaluated first and the result then ORed with the third condition.
You can enforce an order of evaluation with parenthesis like this (I think that is what you want):

WHERE [General Project Information].[Surrogate Rings] = -1 AND
(tblMechanicalFeatures.NoDC1 = 10 OR tblMechanicalFeatures.NoDC2 = 10)

HTH
Thomas

I haven't gotten this to work yet; but I'm still playing with it.

~goes off to search some more~

 

[rapsr59]

Hi Pumpkin,

I'm going to take a stab at it.

Your sql statement

[SIZE=1]SELECT tblMechanicalFeatures.*, [General Project Information].*[/SIZE]
[SIZE=1]FROM tblMechanicalFeatures [/SIZE]
[SIZE=1]INNER JOIN [General Project Information] [/SIZE]
[SIZE=1]ON tblMechanicalFeatures.KeyNumber = [General Project Information].Key Number[/SIZE]
[SIZE=1]WHERE ((([General Project Information].[Surrogate Rings])=-1) [/SIZE]
[SIZE=1]AND ((tblMechanicalFeatures.NoDC1)=10)) [/SIZE]
[SIZE=1]OR (((tblMechanicalFeatures.NoDC2)=10))[/SIZE]
[SIZE=1]ORDER BY tblMechanicalFeatures.KeyNumber;[/SIZE]

Try this:

[SIZE=1]SELECT tblMechanicalFeatures.*, [General Project Information].*[/SIZE]
[SIZE=1]FROM tblMechanicalFeatures [/SIZE]
[SIZE=1]INNER JOIN [General Project Information] [/SIZE]
[SIZE=1]ON tblMechanicalFeatures.KeyNumber = [General Project Information].Key Number[/SIZE]
[SIZE=1]WHERE ((([General Project Information].[Surrogate Rings])=-1) [/SIZE]
[SIZE=1]AND ((tblMechanicalFeatures.NoDC1)=10)) OR [/SIZE]
[SIZE=1][COLOR=blue]((([General Project Information].[Surrogate Rings])=-1)AND[/COLOR] [/SIZE]
[SIZE=1]((tblMechanicalFeatures.NoDC2)=10))[/SIZE]
[SIZE=1]ORDER BY tblMechanicalFeatures.KeyNumber;[/SIZE]

As I said I'm taking stab but I think this might be what you are looking for. Please note that I highlighted, in blue, the portion of the statement I changed.

I also noted that your field "KeyNumber" is different in your two tables. One is KeyNumber - In the tblMechanicalFeatures table and the other Is Key Number ("There is space between the word Key and Number) In the tblMechanicalFeatures table. I mention this because, if they are the same, you will get a Syntax error.

When you mentioned that it was returning all records I assumed you wanted to return specific records that match the criteria you indicated.

Best of luck,


Richard

 

[JustPunkin]

Thanks for the replies. I guess I wasn't clear. I knew WHAT the SQL string should be, I just couldn't get it to work that way.

I looked at it a bit further, and realized I'd probably have to have a few different SQL strings to get this to work, and decided it wasn't a road I wanted to go down.

I ended up revising the table; which makes more sense in hindsight, and also allows my program to work, and will avoid potential future issues.

Thanks again!