Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Question Unanswered: Query Blank Criteria if None is Entered

    I want to be able to run a query where a result is returned whether a criteria is given or not.

    My query is linked to a form so that you can enter a set of values in the form and these will be used in the query. I want to be able to leave a box blank in the form and still get a result using all the values from the table in that field.
    At the mo, it's like this

    Field: <<FIELDNAME>>
    Table: <<TABLENAME>>
    Sort:
    Show:
    Criteria:[Forms]![<<FORMNAME>>]![<<TEXTBOXNAME>>]
    Or:

    P.s the <<>> just mean that it is a generic value for sake of ease.

    I want it to be so that If [Forms]![<<FORMNAME>>]![<<TEXTBOXNAME>>] returns nothing Then it will return all the results available in the field being searched.

    I need to know how to get it to do the above, taking in mind that the query also returns the name of each object with those criteria
    E.G., query results:

    <<NAME OF OBJECT>> <<FIELDNAME>>
    <<OBJECTNAME>> <<RETURNEDVALUE>>

    The query returns numerical and textual results.

    So, it would probably have to run a separate query when a value is blank to return the corresponding name with it.

    So far, the only thing I have learned from help is that it was not designed with someone expanding their MSDB knowledge (it’s useless) and that IsNull returns values that are empty in the format IsNull <Expr> in vbscript and Is Null <Expr> in expression builder.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What you need to do is build a dynamic SQL string, based on the
    values on the form. If you haven't already, put a "View Results"
    button on the form. In the buttons OnClick event, add some coding like this:

    Dim strSql as string, qdfResults as querydef
    Dim strWhere as string
    'build the base select string
    strSql="Select field1, field2, field3 from table1 "
    'now build the where string
    strWhere="Where "
    'loop through each control
    if not isnull(me.txtField1) then
    strwhere=strwhere & "[field1]='" & me.txtfield1 & " and "
    end if
    if not isnull(me.txtfield2) then
    strwhere=strwhere & "[field2]='" & me.txtfield2 & " and "
    end if
    ...

    'now make sure your where string doesn't end in " and "
    if right(strwhere,5)=" and " then
    strwhere = left(strwhere,len(strwhere)-5)
    end if
    if len(strwhere)>6 then
    'strwhere contains more than jus "Where "
    strSql = strsql & strwhere
    currentdb.createquerydef("ViewResult",strSql)
    docmd.openquery "ViewResult"

    Of course you'll need to make sure that ViewResult doesn't already
    exist every time you run this. If so, you'll have to delete it before
    creating it again!

    Mark
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mark, a little tip for your looping algorithm

    start the query like this --

    SELECT foo, bar
    FROM thetable
    WHERE 1=1

    now you can add zero, one, or more additional AND conditions, without having to worry about whether the string ends with AND or whether it has an empty WHERE

    if no conditions are specified, WHERE 1=1 will return all rows, which is exactly what is required when no conditions are specified

    every non-blank condition gets added with AND in front of it, so that each condition is independent of whether there are/were any other conditions added

    neat, eh?

    rudy
    http://r937.com/

  4. #4
    Join Date
    Oct 2003
    Posts
    9
    Is there anyway i can do what i want straight through the query as this would keep things simpler.

    ANYONE?

    Ps the button on the form takes you to a preview of the report which is based on the query.
    Last edited by bobin32; 10-15-03 at 09:47.

  5. #5
    Join Date
    Oct 2003
    Posts
    9

    Unhappy

    is it possible?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes it is possible. Put an inline conditional on your filter criteria in the query such that it returns either the criteria or a blank/empty/null value when the textbox is empty ...

  7. #7
    Join Date
    Oct 2003
    Posts
    9

    Question

    I have had a look at it but cannot see what the line of the query criteria would be. Can u give me an example so that i can use that (giving a null value if the value on the form is blank).

    Thanks

  8. #8
    Join Date
    Oct 2003
    Posts
    9

    Unhappy

    help

  9. #9
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68
    Yeah, great tip. This problem of unknown number of criteria comes up relativley often, so I have to use a number of ifs to compose my SQL string. But this is certainly a more elegant solution. Thanks.

  10. #10
    Join Date
    Oct 2003
    Posts
    9

    Unhappy

    which version of acces is this "inline conditional" feature in? I have access 2000, and can't find where it it to use it. There was a conditional formatting option which i found, but colouring text is not what i want to do.

    Is the "inline conditional" feature included in access 2000 and if so where is it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •