any help really appreciated
:wave::wave::wave:
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
Re: any help really appreciated
Re: any help really appreciated
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....
Re: any help really appreciated
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 :)
Re: any help really appreciated
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
Re: any help really appreciated
Like this
Code:
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
Re: any help really appreciated
u sir are my new hero! Legendary help mate you've saved my bacon