WHERE clause and Checkbox help

[thart21]

I have a search form in which I need to specify in the On Click of my cmd button
WHERE tblContracts.closed (checkbox) is not checked.

Beginning of string:
strSQL = "SELECT tblContracts.negid,tblContracts.supplier AS [Supplier],tblContracts.agreement AS [Agreement Name],tblContracts.followup AS [Follow Up Date],tblContracts.closed FROM tblContracts"

Problem code
I have tried the following lines and no results appear
Try 1) strWhere = "WHERE IsNull [tblContracts.closed] "

Try 2) strWhere = "WHERE tblContracts.closed = 0 "

Try 3) strWhere = "WHERE tblContracts.closed = No "

Try 4) strWHERE = "WHERE tblContracts.closed = 'No' " (+ other variations)

strOrder = "ORDER BY tblContracts.followup;"

The results show up in a list box on the form and it works great until I throw in the criteria for the WHERE clause (without it I just have strWHERE = "WHERE" and more is added on to the search string in the rest of the code)

My checkbox in my table is set to Yes/No.

Thanks in advance for any help with this.

Toni

 

[FoFa]

Checked = True, unchecked = False
WHERE tblContracts.closed = False

 

[thart21]

Hope this doesn't post twice, but I got kicked out on my first attempt.

Thanks for the reply, but unfortunately still no results. I have confirmed that, for the particular search I am conducting, there are records with both True and False results. As I said, the entire code works perfectly, I just can't get it to product results when I add the additional WHERE criteria.

Thanks again.


strWhere = "WHERE tblContracts.closed = False"

Any ideas?

Here is the entire code if it helps:

Private Sub cmdSearch_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()



strSQL = "SELECT tblContracts.negid,tblContracts.supplier AS [Supplier],tblContracts.agreement AS [Agreement Name],tblContracts.followup AS [Follow Up Date],tblContracts.closed FROM tblContracts"


strWhere = "WHERE tblContracts.closed = False"


strOrder = "ORDER BY tblContracts.followup;"


If Not IsNull(Me.txtSupplier) Then
strWhere = strWhere & " (tblContracts.supplier) Like '*" & Me.txtSupplier & "*' AND"
End If

If Not IsNull(Me.txtAgrmt) Then
strWhere = strWhere & " (tblContracts.agreement) Like '*" & Me.txtAgrmt & "*' AND"
End If

If Not IsNull(Me.txtPreparer) Then
strWhere = strWhere & " (tblContracts.preparer) Like '*" & Me.txtPreparer & "*' AND"
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5)


Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

 

[FoFa]

OK, First what happens if you run it without the additional where components, I.E. just the WHERE tblContracts.closed = False

 

[thart21]

If I just put another cmd button and use only the Where tblContracts.closed = False, I can open my form with just the Open records.

Thanks

 

[FoFa]

So by that theroy that part of the code is working as planned (assuming the Flase means open records). So it must be the other parts of the where you are adding that are causing the issue. Try adding one at a time until you figure out which is causing your problems.
I think it is this: strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
Should not that be: strWhere = LEFT(strWhere, Len(strWhere) - 4)

 

[thart21]

Finally got it - had to add "AND" to the WHERE clause.

strWhere = "WHERE tblContracts.closed = False AND"

Thanks so much for all of your help with this!

Toni