help needed with SQL query 1

[humvie]

Hello everyone, my first time here and so far very impressed with the contents of this board.

I've run into a problem and hope someone can tell me where I got sidetracked.

Facts: on a form I have four comboxes and two text boxes from which the user will select/input and then run the report based on their selections. The user does NOT have to select from every field. ie. user has choice to input or select from one, two, three, etc... or all fields.

Problem(s): (I may be wrong here !!) I figure there are about 55 different combinations with 6 fields. How do I write this code so I don't have to rewrite it 55 different ways? I tried using the "Not IsNull" and found that you can't use it here in this fashion (whyyyyyy???)

strSQL2 = "SELECT * FROM Freight WHERE Carrier = '" & Me!cboCarrier & "' AND Origin = '" & Me!cboOrigin & "' OR Carrier = '" & Me!cboCarrier & "' OR Origin = '" & Me!cboOrigin & "'"

The above should give me the option of selecting the following:
1) get me all records where value is equal cboCarrier and value is also equal to cboOrigin. OR
2) get me all records where value is equal to cboCarrier OR
3) get me all records where value is equal to cboOrigin

Supposing I entered a value of "Yellow Freight" for carrier and "New York" for origin and then run the report, I get ALL Yellow Freight records as well as ALL "New York" records. What I really want is ALL records having both Yellow Freight and New York.

And if I only entered Yellow Freight, then I should only get results equal to Yellow Freight.

Any help will be much appreciated.
Many thanks in advance.

 

[mincefish]

Try checking out this post (it was done by me incidentally - I just don't have time to type it all out again!)

Look at the last post, and that should explain how to do it with code.

thread702-319942

Hope this works!

Mincefish

 

[r937]

mincefish, i have a suggestion for you that might slim down your code a bit

you said "The I check to see if anything's already been added to the WHERE part of SQL String. I know this is the first one, so nothing will have been added, but this kinda allows for easier maintenance if you were to add one check in before, or something. If I find that nothings been added, I know that I need to put a WHERE infront of the criteria. If there is something in that string, then we know that at least one set of criteria has been added, so we no longer need to add a WHERE - now we need to add an AND...and the rest of the code is pretty similar!"

here's a trick

when you get past the SELECT and FROM clauses and are ready to generate the WHERE clause, start it off like this --

      WHERE 1=1

that way you don't have to test each subsequent query condition to see if it's the first one

you should write your thread up as a faq

rudy

http://rudy.ca/

 

[humvie]

Thanks guys. Mincefish that was very helpful.
Most of it works fine but I'm having a couple of problems which you may know the answer to.

When I query the dates (textboxes) it works fine but if I leave the boxes blank, I get a syntax error.


If (Not IsNull(Me!Text133.Value)) And (Not IsNull(Me!Text135.Value)) Then
If Len(strSQL2a) = 0 Then
strSQL2a = strSQL2a & " WHERE "
Else
strSQL2a = strSQL2a & " AND "
End If
End If
strSQL2a = strSQL2a & " BegDate BETWEEN #" & Me!Text133.Value & "# AND #" & Me!Text135.Value & "#"


Secondly, I also have a between operator for the weight field. It errors out telling theres a data type mismatch.
The field in the table is set to DOUBLE and so is my reference in the code (ie. DIM Weight as Double). The textboxes are set to default. Any idea what the problem may be?

If (Not (IsNull(Me!txtWgtHIST.Value))) And (Not (IsNull(Me!txtWgtHIST2.Value))) Then
If Len(strSQL2a) = 0 Then
strSQL2a = strSQL2a & " WHERE "
Else
strSQL2a = strSQL2a & " AND "
End If
End If
strSQL2a = strSQL2a & " [Weight in lbs] BETWEEN '" & Me!txtWgtHIST & "' AND '" & Me!txtWgtHIST2 & "'"

THanks again for your help.

 

[mincefish]

humvie,

Right, I got a couple of ideas, which might help, and at least will be a place to start.

1) If you leave the txt boxes blank, and you are getting a syntax error, it sounds to me as though when the code gets to the

Not IsNull(Me!Text133.Value

that the IsNull bit isn't working - have you tried stepping through the code, and leaving those txt boxes blank - in theory it should completely disregard all the code after the If, shouldn't it. So see if it does or not. If it carries on as if there were values in the txt box, then try doing:

Not(Me!Text133.Value = vbNullString)

I would have expected the IsNull one to work, but this is worth a try. Anyhoo, the test I mention above should work, and prove whether it is those IsNulls.

2) I think the reason your weight bit isn't working is because the Weight in the table is a double. You seem to have quotes around the variables - this to me would suggest that it is a string. You are doing this (i've added spaces to make it a little clearer):

[Weight in lbs] BETWEEN ' " & Me!txtWgtHIST & " ' AND ' " & Me!txtWgtHIST2 & " ' "

You have those single quotes - try doing this:

[Weight in lbs] BETWEEN " & Me!txtWgtHIST & " AND " & Me!txtWgtHIST2

Once again, I can't promise anything, but that would be my hunch on this.

Hope you get on OK!

The Mincefish

 

[mincefish]

I've built this into an FAQ Now - does it seem OK?

faq701-2328

 

[ProNetGroup]

Can someone please help me!!! I have read most of the postings about this very topic, and have referred to several books on how to fix it but have gotten NOWHERE! I am trying to create a search form that will take several criteria from a form and create a SQL statement on the fly using the same basic idea as noted above (with the 1=1 and then creating the WHERE statement based on the inputs). However, nothing is seeming to fix my problem. Here is my code to refer to:
Public Sub cmdSubmit_Click()

Dim strWhere As String
Dim strSQL As String
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb()
strSQL = "SELECT * FROM tblProperties WHERE 1=1"

If Not IsNull(Me![txtUtilityName]) Then
strWhere = strWhere & " AND UtilityName = " & txtUtilityName
End If

If Not IsNull(Me![txtCounty]) Then
strWhere = strWhere & " AND County = " & txtCounty
End If

If Not IsNull(Me![txtRegion]) Then
strWhere = strWhere & " AND Region = " & txtRegion
End If

If Not IsNull(Me![txtCustomerPopulation]) Then
strWhere = strWhere & " AND CustomerPopulation = " & txtCustomerPopulation
End If

If Not IsNull(Me![txtOrg]) Then
strWhere = strWhere & " AND Org = " & txtOrg
End If

If Not IsNull(Me![txtStreet]) Then
strWhere = strWhere & " AND Street = " & txtStreet
End If

If Not IsNull(Me![txtCity]) Then
strWhere = strWhere & " AND City = " & txtCity
End If

If Not IsNull(Me![txtTitle]) Then
strWhere = strWhere & " AND Title = " & txtTitle
End If

If Not IsNull(Me![cboOrderBy]) Then
If Me!cboOrderBy = "Utility Name" Then
strWhere = strWhere & " ORDER BY UtilityName"
End If

If Me!cboOrderBy = "County" Then
strWhere = strWhere & " ORDER BY County"
End If

If Me!cboOrderBy = "Region" Then
strWhere = strWhere & " ORDER BY Region"
End If

If Me!cboOrderBy = "Population" Then
strWhere = strWhere & " ORDER BY CustomerPopulation"
End If
End If

strSQL = strSQL & strWhere

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
MsgBox "No matches found, please try again."
Exit Sub
End If

Do Until rs.EOF
MsgBox "Utility:" & rs!UtilityName & " County:" & rs!County
rs.MoveNext
Loop
rs.Close
End Sub

I get the Runtime error 3075, Syntax error (missing operator) in query expression '1=1 AND UtilityName = city of lufkin'. I know that there are alot of options here, but regardless of whether I put in one option or all, I get the same error. It seems like I am just not parsing the SQL statement together properly, and I figure that I am leaving out quotes around something, but I can't figure it out!!! Also, I have checked all of my tables and forms to make sure that the field names and text box names match up. And who knows if my recordset will work at all, because it doesn't try when it discovers the syntax error in the SQL.

One more note, I learned this particular way of programming this in a class of mine and it worked, so I don't know what is going on....Anyone have any suggestions???

Thanks so much!!!!

 

[humvie]

If Not IsNull(Me![txtUtilityName]) Then
strWhere = strWhere & " AND UtilityName = '" & txtUtilityName & "'"
End If

take a look at your "'"

 

[humvie]

..... and you might want to get rid of the IsNull when value is text.