Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Trouble querying multiple items in access

    Hello!

    I am building an access 2007 database that stores information about keys that are loaned to faculty and staff. Each entry has the following information:

    Last Name, First Name, Department, Key Number, Permit Number, Due Date

    I have created a form with 6 text boxes. When I click a button, the query runs and gets its criteria from these textboxes. The crieria for each member of this query is:

    Like (IIf(IsNull([Forms]![Form]![Field]),"*","*" & [Forms]![Form]![Field] & "*"))

    If I fill out every single field then it will find a match.
    I want the query to bring up any matches that it finds in the table, even if some of the fields are not specified.
    Also, some of the table entries are empty (some of the due date values)

    Could anybody please help me with this, I have been trying to find a solution for some time now...
    Last edited by david_p; 02-23-09 at 11:58.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums David!

    First off, you may want to look into normalizing the data. That is to say, as it stands, every time the user needs to input Last Name, First Name, and Department; but this is redundant data in some cases, which presents the opportunity for dirty data. For instance, the User might enter in one transaction with the information
    First Name: John
    Last Name: Smith
    Department: Physics
    Then the same user might need to do another transaction for the same person, but might type instead
    First Name: Jonathan
    Last Name: Smythe
    Department: Natural Sciences
    This is just a suggestions, as it's your choice since you are the database administrator. But this will probably save you many headaches down the road.

    Back to your original question, I personally would be building the query dynamically via code instead of running a stored query referencing form controls. For help on this, I would recommend Martin Green's six part lecture on VBA and SQL.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    6
    thanks, i have solved my issue after reading this tutorial

Posting Permissions

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