Form Query

[Rajeshsms]

Dear all,



I have this form that takes 4 query fields. I was only able to program one. I need to add the other 3 as well. One is a text field and the other 3 are called cbmsecurity , cmbcategory etc. I dont know the syntex.

Should it be str = "SELECT Notes.ID, Notes.[Expanded Number], Notes.Title, Notes.Date, Notes.[Security Level] FROM Notes WHERE ((((Notes.Title) Like '*" & Me.txtTitle & "*' ))) AND (((Notes.Category) = Me.cmbCategory)))"

The title field is free text and the category and security are drop down items


This is throwing all sorts of erros

Help please
Private Sub cmdSearch_Click()
Me.Refresh
Dim str As String

str = "SELECT Notes.ID, Notes.[Expanded Number], Notes.Title, Notes.Date, Notes.[Security Level] FROM Notes WHERE ((((Notes.Title) Like '*" & Me.txtTitle & "*' )))"
DoCmd.ApplyFilter str

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records match search", vbInformation, "Search Again"
End If

Me.txtTotal = Me.RecordsetClone.RecordCount
End Sub

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    'remove 1st And
sWhere= mid(sWhere,4)
  'just use the filter
me.filter = "select * from table where " & sWhere
me.filterOn = true

[ssanfu]

Be aware that "Date" is a reserved word in Access and shouldn't be used as an object name.

[orange]

Rajeshsms,


Tell us more about this database and the tables you have.
What exactly --in simple plain English -- is the problem you are facing?

There are many tutorials on youtube dealing with forms, queries.....

you could look here.