Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Search facility

Discussion in 'Information Technology' started by sebgou, Jul 28, 2006.

  1. sebgou

    sebgou
    Expand Collapse
    Guest

    Hi,

    I've try the code proposed by Allen. It's working well IF I enter a criteria
    in the first field [txtsturbineno]. But if I leave this first criteria blank,
    and I fill the 1 or 2 others criteria, then I won't sort any result. Can you
    help me ? Here's my code adapted from the one proposed by Allen.

    Dim strWhere As String
    Dim lngLen As Long

    If Not IsNull(Me.txtsturbineno) Then
    strWhere = strWhere & "([sturbineno] = """ & _
    Me.txtsturbineno & """) AND "
    End If

    If Not IsNull(Me.txtworkdone) Then
    strWhere = strWhere & "([work] = """ & _
    Me.txtworkdone & """) AND "
    End If

    If Not IsNull(Me.txtdescription) Then
    strWhere = strWhere & "([description] = """ & _
    Me.txtdescription & """) AND "
    End If

    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "Enter some criteria first."
    Else
    DoCmd.OpenForm "frmSHOWRESULT", acFormDS, , Left(strWhere, lngLen)
    End If

    "Allen Browne" wrote:

    > The inferface I like to give my users consists of several unbound controls
    > in the Form Header section of the form, along with a pair of command buttons
    > for Filter and Remove Filter. This Filter button's Click event procedure
    > builds up a string to use as the Filter of the form, based on the controls
    > where the user entered something.
    >
    > The code for the Click event of the command button looks something like
    > this:
    >
    > Private Sub cmdFilter_Click()
    > Dim strWhere As String
    > Dim lngLen As Long
    > Const conJetDateFormat = "\#mm\/dd\/yyyy\#"
    >
    > If Me.Dirty Then Me.Dirty = False 'Save first
    >
    > 'Build up there filter string from the non-blank text boxes.
    > If Not IsNull(Me.txtFilterSurname) Then 'text field example.
    > strWhere = strWhere & "([Surname] = """ & _
    > Me.txtFilterSurname & """) AND "
    > End If
    >
    > If Not IsNull(Me.txtFilterAmount) Then 'number field example
    > strWhere = strWhere & "([Amount] = " & _
    > Me.txtFilterAmount & ") AND "
    > End If
    >
    > If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
    > strWhere = strWhere & "([Birthdate] = " & _
    > Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
    > End If
    >
    > 'Chop off the trailing " AND ".
    > lngLen = Len(strWhere) - 5
    > If lngLen <= 0 Then
    > MsgBox "Enter some criteria first."
    > Else
    > Me.Filter = Left(strWhere, lngLen)
    > Me.FilterOn = False
    > End If
    > End Sub
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "tezza" <tezza@discussions.microsoft.com> wrote in message
    > news:9979059A-B14B-4ECE-9A56-15F62E3E61FB@microsoft.com...
    > >I am trying to provide a user with a search facility on a customer form.
    > > Nothing fancy, just the ability to enter multiple criteria on the form and
    > > then hit a button that basically says Search.
    > > How, please, do I do this???
    > > Has anyone got a good example from start to end.
    > > In the Tools>Startup I have tried to switch off all menus etc so that the
    > > user cannot do anything silly.
    > > I have created a customized toolbar for 'search by form' called Search but
    > > when the form opens, my search icon is innactive.
    > > Can someone please help as this is really causing me problems.

    >
    >
    >
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Much Simpler is

    Dim varWhere AS Variant
    Dim C AS Access.Control
    On Error Resume Next
    varWhere=Null
    For Each C in Me.Controls
    If TypeOf C Is Access.TextBox Then ' Or ComboBox Or Listbox
    If VBA.InStr(C.Tag,"SearchCriteria") > 0 Then
    varWhere= varWhere + " AND " & (C.Name "='" & C.Value & "')"
    End If
    End If
    Next
    '......

    In Your Case (prefixing bound Controls (?!?)) substitute C.Name for
    VBA.Mid(C.Name,4)

    HTH

    Pieter

    "sebgou" <sebgou@discussions.microsoft.com> wrote in message
    news:DE4AACEC-E3DA-4E6A-8AF1-D1C9271C11EC@microsoft.com...
    > Hi,
    >
    > I've try the code proposed by Allen. It's working well IF I enter a
    > criteria
    > in the first field [txtsturbineno]. But if I leave this first criteria
    > blank,
    > and I fill the 1 or 2 others criteria, then I won't sort any result. Can
    > you
    > help me ? Here's my code adapted from the one proposed by Allen.
    >
    > Dim strWhere As String
    > Dim lngLen As Long
    >
    > If Not IsNull(Me.txtsturbineno) Then
    > strWhere = strWhere & "([sturbineno] = """ & _
    > Me.txtsturbineno & """) AND "
    > End If
    >
    > If Not IsNull(Me.txtworkdone) Then
    > strWhere = strWhere & "([work] = """ & _
    > Me.txtworkdone & """) AND "
    > End If
    >
    > If Not IsNull(Me.txtdescription) Then
    > strWhere = strWhere & "([description] = """ & _
    > Me.txtdescription & """) AND "
    > End If
    >
    > lngLen = Len(strWhere) - 5
    > If lngLen <= 0 Then
    > MsgBox "Enter some criteria first."
    > Else
    > DoCmd.OpenForm "frmSHOWRESULT", acFormDS, , Left(strWhere, lngLen)
    > End If
    >
    > "Allen Browne" wrote:
    >
    >> The inferface I like to give my users consists of several unbound
    >> controls
    >> in the Form Header section of the form, along with a pair of command
    >> buttons
    >> for Filter and Remove Filter. This Filter button's Click event procedure
    >> builds up a string to use as the Filter of the form, based on the
    >> controls
    >> where the user entered something.
    >>
    >> The code for the Click event of the command button looks something like
    >> this:
    >>
    >> Private Sub cmdFilter_Click()
    >> Dim strWhere As String
    >> Dim lngLen As Long
    >> Const conJetDateFormat = "\#mm\/dd\/yyyy\#"
    >>
    >> If Me.Dirty Then Me.Dirty = False 'Save first
    >>
    >> 'Build up there filter string from the non-blank text boxes.
    >> If Not IsNull(Me.txtFilterSurname) Then 'text field example.
    >> strWhere = strWhere & "([Surname] = """ & _
    >> Me.txtFilterSurname & """) AND "
    >> End If
    >>
    >> If Not IsNull(Me.txtFilterAmount) Then 'number field example
    >> strWhere = strWhere & "([Amount] = " & _
    >> Me.txtFilterAmount & ") AND "
    >> End If
    >>
    >> If Not IsNull(Me.txtFilterBirthdate) Then 'date field example
    >> strWhere = strWhere & "([Birthdate] = " & _
    >> Format(Me.txtFilterBirthdate, conJetDateFormat) & ") AND "
    >> End If
    >>
    >> 'Chop off the trailing " AND ".
    >> lngLen = Len(strWhere) - 5
    >> If lngLen <= 0 Then
    >> MsgBox "Enter some criteria first."
    >> Else
    >> Me.Filter = Left(strWhere, lngLen)
    >> Me.FilterOn = False
    >> End If
    >> End Sub
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "tezza" <tezza@discussions.microsoft.com> wrote in message
    >> news:9979059A-B14B-4ECE-9A56-15F62E3E61FB@microsoft.com...
    >> >I am trying to provide a user with a search facility on a customer form.
    >> > Nothing fancy, just the ability to enter multiple criteria on the form
    >> > and
    >> > then hit a button that basically says Search.
    >> > How, please, do I do this???
    >> > Has anyone got a good example from start to end.
    >> > In the Tools>Startup I have tried to switch off all menus etc so that
    >> > the
    >> > user cannot do anything silly.
    >> > I have created a customized toolbar for 'search by form' called Search
    >> > but
    >> > when the form opens, my search icon is innactive.
    >> > Can someone please help as this is really causing me problems.

    >>
    >>
    >>
     

Share This Page