Welcome to SPN

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

Sign Up Now!
  1. Monthly (Recurring) Target: $300 :: Achieved: $95
      Become a Supporter    ::   Make a Contribution   

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.

    >>
    >>
    >>
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page