Results 1 to 5 of 5
  1. #1
    rb608 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4

    flexibility of Query based on Form controls.


    I have created a Form from which a user can select the data to use in a Report regarding several buildings. Within the database there is a table for the Facilities (tbl_Facilities). Each Facility may contain several buildings (tbl_Buildings).

    The form allows the user to select by FacilityType, County, and/or BuildingType; and I would like to allow the user to select "all". My question is how I can build this flexibility into the select query that is the data source for the report?

    For example, I can set the select criteria for FacilityType to be Forms!frm_ReportMenu!txtFacilityType; and so long as that control contains data, the query works fine. But how can I set up the query to return all records if the field is left blank?

    TIA.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You could consider having one query that uses user-provided value and another query if the field is left blank.

  3. #3
    rb608 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by Robeen View Post
    You could consider having one query that uses user-provided value and another query if the field is left blank.
    I had considered that. Because the range of options is so great however, I'd need to cover every permutation of blank fields with a separate query. I figured I could use the various states of form completion to set the Data Source for the report in VBA. It's not so many that it's unworkable; I was just hoping for something more "elegant".

  4. #4
    alusser is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    1

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Don't know if of any help, but check out if below gives some guidelines :

    https://www.accessforums.net/showthr...3-Search-Query
    https://www.accessforums.net/showthr...elp-is-needed-!

    Both use the same logic, but are worded differently.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-05-2011, 04:36 PM
  2. Replies: 3
    Last Post: 09-08-2011, 10:53 AM
  3. Replies: 1
    Last Post: 07-25-2011, 09:10 AM
  4. form Controls
    By dref in forum Forms
    Replies: 2
    Last Post: 07-22-2011, 03:31 PM
  5. Controls within form
    By crowegreg in forum Forms
    Replies: 4
    Last Post: 05-31-2011, 08:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums