Two Fields Using OR In Both Not Giving Me Results (1 Viewer)

[Jul]

Okay. I have a frustrating one. This is embarassing to me, because it seems so easy. Anyways, I have a form, where a person could select up to 3 different months and up to 3 different paper types. These combo boxes are listed in the criteria of those fields in my query. If I use the form and try to run my query, it gives me blank results. If I run the query and fill in the pop ups that show up asking for the information that the form is referring to, and I type in the same information, I get the results I expected. What am I missing here? Please help before I go bald!!!

 

[pbaldy]

What is the SQL of the working query, and the SQL of the failing one?

 

[Jul]

GROUP BY tblData.Month, tblData.BasePaper, tblData.TargetCount
HAVING (((tblData.Month)=[Forms]![frmReportByBasePaperAndMonthSelections]![cboFirstMonth] Or (tblData.Month)=[Forms]![frmReportByBasePaperAndMonthSelections]![cboSecondMonth] Or (tblData.Month)=[Forms]![frmReportByBasePaperAndMonthSelections]![cboThirdMonth]) AND ((tblData.BasePaper)=[Forms]![frmReportByBasePaperAndMonthSelections]![Combo8] Or (tblData.BasePaper)=[Forms]![frmReportByBasePaperAndMonthSelections]![Combo10] Or (tblData.BasePaper)=[Forms]![frmReportByBasePaperAndMonthSelections]![Combo12]));

 

[CraigDolphin]

Are your combos storing the correct value? For example, if your table field 'Month' contains numbers like 11 and your combo boxes are storing 'November' instead of 11 then you wouldn't get matching records. Or vice versa if your month field is storing 'November' and the combo box storing the number. Make sure the bound column of your combo box is set to the correct value.

Also, check to be sure that you don't have table-level lookups that might be confusing the issue here.

 

[Jul]

It looks like everything is looking at the correct field. I just tried another one earlier, I had the first combo box as criteria in a field and the query worked perfect. When I added a combo box for another criteria in a different field, it returned blank data as well. So, I am assuming it has something to do with that?

 

[CraigDolphin]

Hmm. Try something for me.

Make sure that all 6 combos on your form that are referenced by your query have something selected in them (no nulls).

If your query works as expected then the problem might be that your criteria doesn't allow for nulls.

 

[Dennisk]

when you refer to the form control in your query specify is null as well :-

in the criteria cell =Forms!YourForm!YourComboBox or Forms!YourForm!YourComboBox is null

When you close the query access will restructure the query adding another column for the null.

If done correctly you can query on all you combo box containing values or not.

 

[Jul]

Craig, I tried what you said, and still get nothing in my results of the query, so I assume it isn't a null issue. It is very strange, in my month criteria, if I put [Enter 1st Month] OR [Enter 2nd Month] OR [Enter 3rd Month] and I type in a month when that comes up, and select one paper type, it returns the correct results....So, it leaves me to believe it is something with one form, filtering a query? Is that possible? So strange!

 

[CraigDolphin]

Jul, can you post a zipped copy of your db (remove any sensitive information)? Maybe it'll be easier to find the problem if we can look at the db hands-on.