HELP! Multiple Parameter Query with Combo Box Form

[dahlt]

USING ACCESS DATABASE 2010 on Windows 2007

I have a lab scheduling database that I am trying to create a search form for with a multiple parameter query attached to that search form.

I have the following fields in the Master_Template table that is attached to the Master_Template_Search Query.
Master_Template Table:
OrgName
UserGroup
Comments
Day
Date
StartTime
EndTime
Hours
Lab
Periph
FAN
Master_Template_Query only includes:
OrgName
Comments
Day
Date
StartTime
EndTime
Hours
Lab

I had created a Form with Combo Boxes and in each combo box property I have set the CONTROL SOURCE to point to the correct corresponding field in the query so for OrgName Combo box property I have Control Source pointing to OrgName, I have row source type set to value list, and the row source has ALL the possible ORGNAMES - of course. The same is true for all other fields included in the query. FORM NAME is NEWSEARCH.
I have a control box with properties to set to a macro to run said query.
In each Criteria field within the QUERY I have the following Expression:
[Forms]![NEWSEARCH]![Orgname] and this is my SQL code:

SELECT Master_Template.ORGNAME, Master_Template.Comments, Master_Template.Day, Master_Template.Date, Master_Template.StartTime, Master_Template.EndTime, Master_Template.Hours, Master_Template.Lab, *
FROM Master_Template
WHERE (((Master_Template.ORGNAME)=[Forms]![NEWSEARCH]![Orgname]) AND ((Master_Template.Comments)=[Forms]![NEWSEARCH]![Comments]) AND ((Master_Template.Day)=[Forms]![NEWSEARCH]![Day]) AND ((Master_Template.Date)=[Forms]![NEWSEARCH]![Date]) AND ((Master_Template.StartTime)=[Forms]![NEWSEARCH]![StartTime]) AND ((Master_Template.EndTime)=[Forms]![NEWSEARCH]![EndTime]) AND ((Master_Template.Hours)=[Forms]![NEWSEARCH]![Hours]) AND ((Master_Template.Lab)=[Forms]![NEWSEARCH]![Lab]))
ORDER BY Master_Template.Date, Master_Template.StartTime, Master_Template.Lab;


In theory you would think that this would bring back the results of whatever choices the user would select - not all fields HAVE to have data except for the ORGNAME, DAY AND DATE of course.
Well this query/form works ONLY if I use ONE PARAMETER - once I have multiple parameters in all of the criteria row using the AND clause in the SQL code I get NO results back no matter what combo boxes I fill out.
If I use the OR clause I get TOO MANY results back.

Right now I only have ONE field in use for a parameter query/form selection which is the ORGNAME field and of course this does bring back all of that team's lab shots for that one week! This works "for now" but I would like to have the result scale down to just that one lab shot someone is looking for which would be one specific record.

Does ANYONE have any answers to this? What am I doing wrong?

Sincerely APPRECIATE!!

Tracy (dahlt)

 

[AlexN]

Well, that's about the case I had here http://www.access-programmers.co.uk/forums/showthread.php?t=272420 and got no solution so far. Thankfully I had the option of using multiple queries. Curious of a solution though.

Noticed that you don't use the Is Null expression in your sql code though, just in case someone doesn't fill a criteria combo.

Good Luck with your issue. Sorry I don't have any suggestions.

 

[dahlt]

I will try the IS NULL inclusion maybe it will help - but it seems to be an issue for more than one person in Access Database, huh? I don't understand though why some people are successful at it. I just tried to attach a youtube link but I'm only a level 3?
Maybe it's a different version of Access? - AND Wow multiple queries for ONE query result? Is that your workaround at this point?

I may have to go that route but you know Access and its already a full database as it is.

Thank you for replying though!!

 

[AlexN]

Maybe it's a different version of Access? - AND Wow multiple queries for ONE query result? Is that your workaround at this point?

I used multiple queries because I wanted multiple results from the same tables, depending on multiple (slightly different) criteria, and because I COULD do it in MY CASE. I certainly DO NOT recommend it in your case. And definitely is not a different version issue 'cause I've tried 2010 and 2013.

Wait for the experts to look at it.

Have fun!

 

[Frothingslosh]

Have you tried setting this up as a parameter query, then using VBA to assign parameters directly? If that doesn't work, you could also construct your SQL statement programmatically based on which controls have parameters entered.