Unanswered: Trouble querying multiple items in access
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...
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
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.