Optional Search

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Woodies_46@hotmail.com
    #1

    Optional Search

    Hello all,

    I've just hit a brick wall. What I have is a form with a heap of tick
    boxs and text box on it and down the bottom a subform with shows the
    results. i also have a search button down the buttom as well.

    Now what I'd like to be able to do is for the user to select and fill
    as many fields as they see fit then hit search and it will show the
    results in the subform down the buttom that comply to one or all of the
    search critea.

    The trouble i'm having is with the whole optional thing in the query..
    the few thing i have tryed have just shown me null values.

    If someone could help me it would be great.

    Cheers

    Tags: None
    • Tom van Stiphout
      #2
      Re: Optional Search

      On 26 Mar 2006 17:44:32 -0800, Woodies_46@hotm ail.com wrote:

      When I have many criteria and I am in an MDB environment (SQL Server
      has the elegant COALESCE function), I typically use a dynamic SQL
      statement. Something like:
      Me.MySubform.Fo rm.RecordSource = sql

      This sql statement has the standard select statement:
      sql = "select * from somequery where "

      and then the dynamic where clause:
      sql = sql & "MyField1=" & MyCheckbox1 & " and "
      sql = sql & "MyField2=" & MyCheckbox2 & " and "
      sql = sql & "MyField3=" & MyCheckbox3 & " and "
      'you get the point

      At the end just strip the trailing " and ".
      To debug this statement, first print it to the debug window:
      debug.print sql

      and then paste it into a new query in sql view. If you have any syntax
      problems, the query parser will point them out. For example if you
      have text values, they need to be in quotes.

      -Tom.

      [color=blue]
      >Hello all,
      >
      >I've just hit a brick wall. What I have is a form with a heap of tick
      >boxs and text box on it and down the bottom a subform with shows the
      >results. i also have a search button down the buttom as well.
      >
      >Now what I'd like to be able to do is for the user to select and fill
      >as many fields as they see fit then hit search and it will show the
      >results in the subform down the buttom that comply to one or all of the
      >search critea.
      >
      >The trouble i'm having is with the whole optional thing in the query..
      >the few thing i have tryed have just shown me null values.
      >
      >If someone could help me it would be great.
      >
      >Cheers[/color]

        Comment

        Previous template Next
        Working...