any help really appreciated

[Baggers]

Hi guys

I've designed a form in access that filters records from a corresponding table in the same database. Each individual filter works really well but i want them to work in conjunction with one another so for example searching a surname and date at the same time. I've pasted the script below and would appreciate any advice you can give me with it

all the best

baggers

Option Compare Database

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([PtSurname] Like ""*" & Me.txtSurname & "*"")"
End If
If Not IsNull(Me.txtFilterSite) Then
strWhere = strWhere & "([Site] Like ""*" & Me.txtFilterSite & "*"")"
End If
If Not IsNull(Me.TextUserName) Then
strWhere = strWhere & "([User] Like ""*" & Me.TextUserName & "*"")"
End If
If Not IsNull(Me.Textdate) Then
strWhere = strWhere & "([TransferDate] Like ""*" & Me.Textdate & "*"")"
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

[kleinma]

moved from VB.NET

[GaryMazzone]

If you have more then 1 condition in a WHERE clause they need to have an AND between each one. The word WHERE can only appear once...


Also should not be in VB.Net but in Database forum.


Matt beat me to the move....

[Baggers]

i dont understand how that will work though as doesn't each text box need to look at the individual column in the table? could you show me what to remove? cheers for helping i'm new to the game and am finding it pretty difficult

[Baggers]

I'm sorry could you give me a little example of what you mean, I'm really new to this and thought that if i deleted each where then it wouldnt look up the text box in the individual table field? probably not!! thanks so much

[GaryMazzone]

Like this

Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([PtSurname] Like ""*" & Me.txtSurname & "*"")"
End If
If Not IsNull(Me.txtFilterSite) Then
  If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND "
  End If 
      strWhere = strWhere & "([Site] Like ""*" & Me.txtFilterSite & "*"")"
End If
If Not IsNull(Me.TextUserName) Then
  If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND "
  End If 
  strWhere = strWhere & "([User] Like ""*" & Me.TextUserName & "*"")"
End If
If Not IsNull(Me.Textdate) Then
  If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND "
  End If 
   strWhere = strWhere & "([TransferDate] Like ""*" & Me.Textdate & "*"")"
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

[Baggers]

u sir are my new hero! Legendary help mate you've saved my bacon