+ Reply to Thread
Results 1 to 4 of 4

Excel Query, Return all if parameter is blanks

  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    everett, WA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Excel Query, Return all if parameter is blanks

    So i have several criteria in my query. the criteria take parameters which are indicated using a drop down in an excess cell.
    I would like it so that if the cell is blank, that the criteria not be filtered and all items are returned.

    Example in my SQL command
    SELECT....
    FROM.. "repair_tech"."surname" = ?

    The variable is the tech's surname. I want the table to display all results if no surname is specified.

    thanks
    Maya

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,924

    Re: Excel Query, Return all if parameter is blanks

    Your criteria should be in your WHERE clause not in your FROM clause.

    Look here http://www.w3schools.com/sql/default.asp
    Alan


    Have you debugged your code?
    Database Normalization
    What is Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    02-12-2013
    Location
    everett, WA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Excel Query, Return all if parameter is blanks

    Yes you are right, that was a typo on my part.
    I meant to write my pseudo code is
    SELECT....
    FROM..
    WHERE "repair_tech"."surname" = ?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,924

    Re: Excel Query, Return all if parameter is blanks

    I don't use SQL in Excel (disclaimer), but in Access in my WHERE clause I would use

    WHERE [repair_tech].[surname] like "*" & Criteria & "*"

    the asterisks work as wildcards and if nothing is entered in the criteria, then all records are returned.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1