Inconsistent Query Results.

[Richard Horne]

Hi guys,

I have the following query:

SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like "*" & [cboQueriedCustomer]) AND
((Order_Details.[Item Type]) Like "*" & [cboProduct]) AND
((Order_Details.Description) Like "*" & [txtDescription] & "*") AND
((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*") AND
((Orders.Complete) Like "*" & [cboOrder_Complete]) AND ((Orders.[Customer's
Order Number]) Like "*" & [QueriedCustRef]) AND ((Orders.Contact) Like "*" &
[cboQueriedContact]))
ORDER BY Orders.OrderNumber DESC;

Which works great apart from one very puzzling aspect of it.

The above query takes various inputs and filters the results accordingly.
One of the fields above cboQueriedContact lists all the employees in our
company (about 10) and then filters the records based on whether the order is
assigned to that person, but for some bizarre reason, two of the staff
members results are always identical.

Everyone else's results work fine, but theirs always match each other, even
though one of the person's names is blatantly not on the list of orders the
query displays.

The only thing I can think is causing a problem is that the id of the two
users is 6 and 16 and I wonder if the fact that both contain a 6 could be the
caue? However I tested this with users 7 and 17 and the problem doesn't occur
for them.

Is there an inherent problem with the query above or is it likely to be
something to do with the structure of my Orders and Order_Details table? It's
really frustrating.

 

[[MVP] S.Clark]

If that is an ID, then omit using the Like. Because Like *6 would include
16.

 

[Chris M]

Hi Richard,

In your query you say:
.....((Orders.Contact) Like "*" & [cboQueriedContact]))....

Why the '*'?

This means that if cboQueriedContact is 6 (say) then it will match
Orders.Contact of 6, 16, 26 etc.

This might have somthing to do with your problem, but I would expect 1,11
2,12 7,17 etc to have the same problem, which you say it doesn't.

What happens if you drop the wildcard anyway??

Cheers,

Chris.

 

[Richard Horne]

Hi guys,

I actually realised the problem myself in my sleep last night.

It was because I was using a wildcard and as you rightly said, 6 is like 16.

So when I changed for formula from like to = it works perfectly.
Seems so simple in hindsight.

cheers guys.

Hi Richard,

In your query you say:
.....((Orders.Contact) Like "*" & [cboQueriedContact]))....

Why the '*'?

This means that if cboQueriedContact is 6 (say) then it will match
Orders.Contact of 6, 16, 26 etc.

This might have somthing to do with your problem, but I would expect 1,11
2,12 7,17 etc to have the same problem, which you say it doesn't.

What happens if you drop the wildcard anyway??

Cheers,

Chris.

Hi guys,

I have the following query:
,
SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like "*" & [cboQueriedCustomer]) AND
((Order_Details.[Item Type]) Like "*" & [cboProduct]) AND
((Order_Details.Description) Like "*" & [txtDescription] & "*") AND
((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*")
AND
((Orders.Complete) Like "*" & [cboOrder_Complete]) AND
((Orders.[Customer's
Order Number]) Like "*" & [QueriedCustRef]) AND ((Orders.Contact) Like "*"
&
[cboQueriedContact]))
ORDER BY Orders.OrderNumber DESC;

Which works great apart from one very puzzling aspect of it.

The above query takes various inputs and filters the results accordingly.
One of the fields above cboQueriedContact lists all the employees in our
company (about 10) and then filters the records based on whether the order
is
assigned to that person, but for some bizarre reason, two of the staff
members results are always identical.

Everyone else's results work fine, but theirs always match each other,
even
though one of the person's names is blatantly not on the list of orders
the
query displays.

The only thing I can think is causing a problem is that the id of the two
users is 6 and 16 and I wonder if the fact that both contain a 6 could be
the
caue? However I tested this with users 7 and 17 and the problem doesn't
occur
for them.

Is there an inherent problem with the query above or is it likely to be
something to do with the structure of my Orders and Order_Details table?
It's
really frustrating.