Why is this simple query not working?

[doobybug]

I have a table called frmSearch and in it I am searching txtTitle
This is the query which when run it gives me all the contents of the table

[andy49]

Add the or line to the line below

[doobybug]

Still no luck...even if I remove is null and leave the Like part it does not work...always giving me all the records

[aytee111]

Timing issue? Form open, value in field, field does not have the focus, run query

[doobybug]

The focus was a problem...when I leave the focus on the field it runs perfectly...but what about if I want to add more fields and more criteria and add a search button? The focus cannot be on all fields I presume

[aytee111]

Clicking on the button will move the focus to the button.

[doobybug]

My point is that now I have the following form...as you can see I have more fields to add in the search but I am just using 2 as a sample.
The button to run the query is the edit button. When I enter the contract No on its own, the query work well. When I input the title it gives me a blank record even if the title is there. When I input both fields it takes the field of the contractNo and works - The AND is not working...is it an issue of focus?

[aytee111]

The point I was making about the focus is that if you type "abc" and then run the query, the text hasn't been recognized yet, that is why moving off the field is important.

The way you have your criteria above is that both fields must be entered. Multiple fields can get complicated. For me it is easier to create the SQL in VBA and be able to build up the string one field at a time.

[doobybug]

I was trying to do just that while I was waiting for your answer...I have debugging errors but I do not know if it makes sense...can you take a look at it please?

Private Sub btnEdit_Click()    Dim contractNo As Integer
    Dim title As String
    Dim sql As String
    
    sql = "SELECT [tblActInfo.Title], [tblActInfo.ContractNo] FROM [tblActInfo] WHERE (((tblActInfo.Title) Is Null) And ((tblActInfo.ContractNo) Is Null)) Or (((tblActInfo.Title) Like " * " & title  & " * ") And ((tblActInfo.ContractNo)=contractNo))"
    CurrentDb.Execute sql
    
End Sub

[aytee111]

What I was thinking was to build up the WHERE string as a variable. You said you had a number of fields that were going to be used in the search, each must be checked to see if it is empty and in that case will be ignored.

Here is what I was thinking (taken from https://stackoverflow.com/questions/14125738/query-by-form-for-multiple-fields-in-access-2010):

Dim SQL As String

SQL = "SELECT tblName.* From tblName WHERE (1=1)"

If Not IsNull(Me.combo1) Then
    SQL = SQL & " And ([Field1] Like ""*" & Me.combo1 & "*"")" ' I am using like statements here, but that is because this is a search tool.
End If

If Not IsNull(Me.combo2) Then
    SQL = SQL & " And ([Feild2] Like ""*" & Me.combo2 & "*"")"
End If

Docmd.RunSQL SQL

[doobybug]

I tried something similar to the code provided but I am getting a syntax error in the title field...any ideas why?

strSQL = "SELECT [Title], [ContractNo] FROM [tblActInfo] WHERE ((([Title] Is Null) and ([ContractNo] Is Null)) Or (([Title] Like ""*" & Me.txtTitle & "" * ") and ([ContractNo] = " & contractNo & ")))"

[aytee111]

(([Title] Like '*" & Me.txtTitle & "*') and ([ContractNo] = " & contractNo & ")))"

[doobybug]

Nope still doesn't work...it is driving me insane

The only way I got it to work is in another query with this criteria...Like "*" & "cattle" & "*"
I am trying to replicate it in sql...any help pls?

[doobybug]

I managed to do this...but now I have another problem...when title is filled in and contractNo no it is not working as in the criteria I am getting "*"&""&"*"

[aytee111]

See post #10