QUERY MULTIPLE CRITERIA

[ZigZagZak]

I have a Query that filters a form using a combination of multiple fields as
criteria. If I use any more than 6 fields it becomes "to complex". Any way
around this......maybe a better way of coding it?

Any help is appreciated. Thanks in advance!


SELECT REBORE.BRANCH, REBORE.DATE, REBORE.EMPLOYEE, REBORE.ORDER,
REBORE.[PART NUMBER], REBORE.QUANTITY, REBORE.OPERATION, REBORE.MACHINE,
REBORE.SETUP, REBORE.START, REBORE.COMPLETE, REBORE.DESCRIPTION, REBORE.[LINE
ITEM], REBORE.[BORE SIZE], REBORE.ANSI
FROM REBORE
WHERE
([REBORE].[BRANCH] =
[Forms]![REBORE INQUIRY]![BRANCH]
OR [Forms]![REBORE INQUIRY]![BRANCH] IS NULL)
AND
([REBORE].[ORDER] =
[Forms]![REBORE INQUIRY]![ORDER]
OR [Forms]![REBORE INQUIRY]![ORDER] IS NULL)
AND
([REBORE].[EMPLOYEE] =
[Forms]![REBORE INQUIRY]![EMPLOYEE]
OR [Forms]![REBORE INQUIRY]![EMPLOYEE] IS NULL)
AND
([REBORE].[PART NUMBER] =
[Forms]![REBORE INQUIRY]![PART]
OR [Forms]![REBORE INQUIRY]![PART] IS NULL)
AND
([REBORE].[MACHINE] =
[Forms]![REBORE INQUIRY]![MACHINE]
OR [Forms]![REBORE INQUIRY]![MACHINE] IS NULL)
AND
([REBORE].[OPERATION] =
[Forms]![REBORE INQUIRY]![OPERATION]
OR [Forms]![REBORE INQUIRY]![OPERATION] IS NULL)
AND
([REBORE].[DATE]BETWEEN
[FORMS]![REBORE INQUIRY]![STARTDATE] AND
([FORMS]![REBORE INQUIRY]![ENDDATE]+1)
OR [FORMS]![REBORE INQUIRY]![STARTDATE] AND [FORMS]![REBORE
INQUIRY]![ENDDATE] IS NULL) ;

 

[Jerry Whittle]

You have a few field names that are reserved words. For example DATE, ORDER,
and DESCRIPTION. Probably too late to rename these field, but at least put
square brackets [] around them to make sure that's not the problem. More
about reserved words here:
http://support.microsoft.com/kb/286335/

([REBORE].[DATE]BETWEEN

Maybe a typo, but there's no space before BETWEEN.

You could try rewriting the following like so:

WHERE ([REBORE].[BRANCH] = [Forms]![REBORE INQUIRY]![BRANCH]
OR [Forms]![REBORE INQUIRY]![BRANCH] IS NULL)

WHERE ([REBORE].[BRANCH] Like "*" & [Forms]![REBORE INQUIRY]![BRANCH] & "*")

The problem is if BRANCH was Tim, it would also return Time, Timid, etc.


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a Query that filters a form using a combination of multiple fields as
criteria. If I use any more than 6 fields it becomes "to complex". Any way
around this......maybe a better way of coding it?

Any help is appreciated. Thanks in advance!


SELECT REBORE.BRANCH, REBORE.DATE, REBORE.EMPLOYEE, REBORE.ORDER,
REBORE.[PART NUMBER], REBORE.QUANTITY, REBORE.OPERATION, REBORE.MACHINE,
REBORE.SETUP, REBORE.START, REBORE.COMPLETE, REBORE.DESCRIPTION, REBORE.[LINE
ITEM], REBORE.[BORE SIZE], REBORE.ANSI
FROM REBORE
WHERE
([REBORE].[BRANCH] =
[Forms]![REBORE INQUIRY]![BRANCH]
OR [Forms]![REBORE INQUIRY]![BRANCH] IS NULL)
AND
([REBORE].[ORDER] =
[Forms]![REBORE INQUIRY]![ORDER]
OR [Forms]![REBORE INQUIRY]![ORDER] IS NULL)
AND
([REBORE].[EMPLOYEE] =
[Forms]![REBORE INQUIRY]![EMPLOYEE]
OR [Forms]![REBORE INQUIRY]![EMPLOYEE] IS NULL)
AND
([REBORE].[PART NUMBER] =
[Forms]![REBORE INQUIRY]![PART]
OR [Forms]![REBORE INQUIRY]![PART] IS NULL)
AND
([REBORE].[MACHINE] =
[Forms]![REBORE INQUIRY]![MACHINE]
OR [Forms]![REBORE INQUIRY]![MACHINE] IS NULL)
AND
([REBORE].[OPERATION] =
[Forms]![REBORE INQUIRY]![OPERATION]
OR [Forms]![REBORE INQUIRY]![OPERATION] IS NULL)
AND
([REBORE].[DATE]BETWEEN
[FORMS]![REBORE INQUIRY]![STARTDATE] AND
([FORMS]![REBORE INQUIRY]![ENDDATE]+1)
OR [FORMS]![REBORE INQUIRY]![STARTDATE] AND [FORMS]![REBORE
INQUIRY]![ENDDATE] IS NULL) ;

 

[ZigZagZak]

ya I already fixed that after I posted the question. Also to get around the
problem temporarily I took out the "or is null" on the date range to make it
a required criteria. It works, but I would like to be able to have more than
6 criteria fields at some point.

You have a few field names that are reserved words. For example DATE, ORDER,
and DESCRIPTION. Probably too late to rename these field, but at least put
square brackets [] around them to make sure that's not the problem. More
about reserved words here:
http://support.microsoft.com/kb/286335/

([REBORE].[DATE]BETWEEN

Maybe a typo, but there's no space before BETWEEN.

You could try rewriting the following like so:

WHERE ([REBORE].[BRANCH] = [Forms]![REBORE INQUIRY]![BRANCH]
OR [Forms]![REBORE INQUIRY]![BRANCH] IS NULL)

WHERE ([REBORE].[BRANCH] Like "*" & [Forms]![REBORE INQUIRY]![BRANCH] & "*")

The problem is if BRANCH was Tim, it would also return Time, Timid, etc.


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a Query that filters a form using a combination of multiple fields as
criteria. If I use any more than 6 fields it becomes "to complex". Any way
around this......maybe a better way of coding it?

Any help is appreciated. Thanks in advance!


SELECT REBORE.BRANCH, REBORE.DATE, REBORE.EMPLOYEE, REBORE.ORDER,
REBORE.[PART NUMBER], REBORE.QUANTITY, REBORE.OPERATION, REBORE.MACHINE,
REBORE.SETUP, REBORE.START, REBORE.COMPLETE, REBORE.DESCRIPTION, REBORE.[LINE
ITEM], REBORE.[BORE SIZE], REBORE.ANSI
FROM REBORE
WHERE
([REBORE].[BRANCH] =
[Forms]![REBORE INQUIRY]![BRANCH]
OR [Forms]![REBORE INQUIRY]![BRANCH] IS NULL)
AND
([REBORE].[ORDER] =
[Forms]![REBORE INQUIRY]![ORDER]
OR [Forms]![REBORE INQUIRY]![ORDER] IS NULL)
AND
([REBORE].[EMPLOYEE] =
[Forms]![REBORE INQUIRY]![EMPLOYEE]
OR [Forms]![REBORE INQUIRY]![EMPLOYEE] IS NULL)
AND
([REBORE].[PART NUMBER] =
[Forms]![REBORE INQUIRY]![PART]
OR [Forms]![REBORE INQUIRY]![PART] IS NULL)
AND
([REBORE].[MACHINE] =
[Forms]![REBORE INQUIRY]![MACHINE]
OR [Forms]![REBORE INQUIRY]![MACHINE] IS NULL)
AND
([REBORE].[OPERATION] =
[Forms]![REBORE INQUIRY]![OPERATION]
OR [Forms]![REBORE INQUIRY]![OPERATION] IS NULL)
AND
([REBORE].[DATE]BETWEEN
[FORMS]![REBORE INQUIRY]![STARTDATE] AND
([FORMS]![REBORE INQUIRY]![ENDDATE]+1)
OR [FORMS]![REBORE INQUIRY]![STARTDATE] AND [FORMS]![REBORE
INQUIRY]![ENDDATE] IS NULL) ;