Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: Select query issue

    Hi,

    I am making a form that when data is input it uses that data as criteria in a query and gives a report that is based on that query. However, when nothing is entered into say the "name" field then no results appear. Obviously this is because it is searching for blank name fields but not coming up with any thus no results to display. Is there a way around this, so that when no data is entered in the "Name" field then ALL the names are given in the report.

    Your help is greatly appreciated,

    Josh.

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In the table, for this field put YES in Required properties.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you currently have blank data in a field (mixed with non-blank data) and you want to setup criteria in a query based upon a 'search' type value in an unbound field on a form (so it shows all data if the search field is blank), there is a little trick you can use in your query:

    Put in this expression in a column of your query:

    IIF(isnull(Forms!MyFormName!MySearchFieldName),"X" ,[MyDataFieldsName])

    then for criteria under this column put: Like Forms!MyFormName!MySearchFieldName or = "X"

    (note: you can use something other than "X" if that conflicts with your data values.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2010
    Posts
    6
    thanks for your helps guys.

  5. #5
    Join Date
    Mar 2010
    Posts
    6
    it has now come to my attention that that trick, pkstormy, is genius. Thank you very much.

Tags for this Thread

Posting Permissions

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