Multi combo box query

[biggcc]

Thanks for looking at it ThreeCrow but when I run it I still have problems even on the one I sent you. Take a look at the Master table and find the number of entries for Joseph Hitt. Then run the report and just select him and nothing else. It only returns 5 entries instead of 6 found in the master table. That's what I'm trying to figure out. In the full database I have a salesperson named Bevery Kuzniar and she's the rep listed in 64 entries but when I run the report and just select her it gives me only 21. I can't figure it out. I thought it might be caused by the date range but it did the same thing even when I entered a date range.

Any thoughts?

Thanks,
Chester Campbell

 

[RV]

It only returns 5 entries instead of 6 found in the master table.

Correction, there are 7 entries in your table, not 6.
The problem lies in your query RepDialog, more particular in the way you try you pass a wildcard in your clauses.
The wildcard will not work for any table field having a null value.

Two approaches

First approach

Intoduce the Nz function for all your clauses.
Example, you've got this clause:

([Master].[ReasonBuying]) Like IIf(IsNull([forms]![repdialog]![reasonbuying]),"*",[forms]![repdialog]![reasonbuying]))

Change it into

((Nz([Master].[ReasonBuying],0)) Like IIf(IsNull([forms]![repdialog]![reasonbuying]),"*",[forms]![repdialog]![reasonbuying]))

Repeat for all your clauses

Second (better) approach

Amend your query so it will compare on form field not being entered hence to be excluded from the selection.
For instance, this clause

And (([Master].[ReasonBuying]) Like IIf(IsNull([forms]![repdialog]![reasonbuying]),"*",[forms]![repdialog]![reasonbuying]))

should read

And ([Master].[ReasonBuying] LIKE [forms]![repdialog]![reasonbuying] Or [forms]![repdialog]![reasonbuying] Is Null)

I'm not sure why you're using the LIKE operator.
It doesn't make sense cause you're doing full comparisons in your query.
Rather confusing, replace it by the = operator.
Also, you've got a bunch of extra left and right brackets, remove them, they are redundant and only will make your statement more difficult to read and understand.

Finally, reconsider your design, what you've got right now is a flat table.
Normalize your database structure in order to prevent severe design issues and data retrieving issues along the way....

RV

 

[biggcc]

Thanks for your input RV. I did take your advice and use the second approach (even changed the like to =) and everything is working correctly.

Thanks again for everyone's input here - I've got exactly what they've been looking for now.

Regards,
Chester Campbell