query criteria Not Like ... <>

[Kevin]

I'm building a query to filter the contents of a table of order numbers and
part numbers. The filter is a subset of the part numbers in the main table.
I need to apply the filter two different ways based on the contents of a
separate table called Toggle. If the contents of Toggle is 1 nothing is
filtered and the query returns all order numbers. If Toggle is 2 the query
returns order numbers associated with part numbers in common with the filter.
If Toggle is 3 the query returns order numbers associated with part numbers
NOT in common with the filter.


The query includes three tables; ORDERS, FILTER and TOGGLE. All tables are
linked to external data that I cannot change. The ORDERS table contains
fields ORDERNUM and PARTNUM which I've added to my query. For the sake of
this discussion the possible values of PARTNUM are AB, AC, AD, AE, AF and AG.
The FILTER table can contain one, two but no more than three part numbers.
The TOGGLE table contains a single value; 1, 2 or 3.

PARTNUM criteria looks like this.

IIf([TOGGLE]![PARTNUMTOGGLE]=1,([ORDERS].[PARTNUM]),IIf([TOGGLE]![PARTNUMTOGGLE]=2,([ORDERS].[PARTNUM])
Not Like [FILTER]![PARTNUMFILTER],[FILTER]![PARTNUMFILTER]))

The query seems to work for PARTNUMTOGGLE values of 1 and 3. But when
PARTNUMTOGGLE = 2 the query returns nothing. I've also tried using "<>" in
lieu of "Not Like" but with the same results.

Any help with be greatly appreciated.

Thank you.


Kevin

 

[Kevin]

I've opted for a slightly different approach using intermediate queries.

IntermediateQueryA returns part numbers, unfiltered, from [ORDERS].PARTNUM.
IntermediateQueryB compares results from IntermediateQueryA with
[FILTER].PARTNUMFILTER. That is, IntermediateQueryB fields are
[IntermediateQueryA].PARTNUM (criteria = Is Not Null) and
[FILTER].PARTNUMFILTER (criteria = Is Null).

IntermediateQueryB SQL looks like this:

SELECT [ORDERS].PARTNUM
FROM [ORDERS] LEFT JOIN [FILTER] ON [ORDERS].PARTNUM = [FILTER].PARTNUMFILTER
WHERE ((([ORDERS].PARTNUM) Is Not Null) AND (([FILTER].PARTNUMFILTER) Is
Null));

Lastly, IntermediateQueryB and existing table [TOGGLE].PARTNUMTOGGLE are
then used in FinalQueryC which returns the effective list of part numbers.

FinalQueryC SQL looks like this:

SELECT
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER)) AS [FinalQueryC]
FROM [TOGGLE], [FILTER], [IntermediateQueryB], [IntermediateQueryA]
GROUP BY
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER))
HAVING
(((IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER))) Is Not Null))
ORDER BY
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER));

FinalQueryC gets me to my intended bottom line.

I'm seeing a LIKE clause, but I'm not seeing any wildcards. Could that be it?


Not Like "*" & [FILTER]![PARTNUMFILTER] & "*"

I'm building a query to filter the contents of a table of order numbers and
part numbers. The filter is a subset of the part numbers in the main table.
I need to apply the filter two different ways based on the contents of a
separate table called Toggle. If the contents of Toggle is 1 nothing is
filtered and the query returns all order numbers. If Toggle is 2 the query
returns order numbers associated with part numbers in common with the filter.
If Toggle is 3 the query returns order numbers associated with part numbers
NOT in common with the filter.

The query includes three tables; ORDERS, FILTER and TOGGLE. All tables are
linked to external data that I cannot change. The ORDERS table contains
fields ORDERNUM and PARTNUM which I've added to my query. For the sake of
this discussion the possible values of PARTNUM are AB, AC, AD, AE, AF and AG.
The FILTER table can contain one, two but no more than three part numbers.
The TOGGLE table contains a single value; 1, 2 or 3.

PARTNUM criteria looks like this.

IIf([TOGGLE]![PARTNUMTOGGLE]=1,([ORDERS].[PARTNUM]),IIf([TOGGLE]![PARTNUMTOGGLE]=2,([ORDERS].[PARTNUM])
Not Like [FILTER]![PARTNUMFILTER],[FILTER]![PARTNUMFILTER]))

The query seems to work for PARTNUMTOGGLE values of 1 and 3. But when
PARTNUMTOGGLE = 2 the query returns nothing. I've also tried using "<>" in
lieu of "Not Like" but with the same results.

Any help with be greatly appreciated.

Thank you.

Kevin

 

[Kevin]

Thanks for the suggestion. I'm fluent in Excel and Excel VBA but just
starting digging in to Acess. I like it. Queries today...VBA tomorrow.

Glad you got where you needed to be.

This is an example of why I avoid IIf statements unless they are simple.
Otherwise I use dynamic SQL and build it using VBA If statements, or Case
statments that are much easier to read and navigate through.

I've opted for a slightly different approach using intermediate queries.

IntermediateQueryA returns part numbers, unfiltered, from [ORDERS].PARTNUM.
IntermediateQueryB compares results from IntermediateQueryA with
[FILTER].PARTNUMFILTER. That is, IntermediateQueryB fields are
[IntermediateQueryA].PARTNUM (criteria = Is Not Null) and
[FILTER].PARTNUMFILTER (criteria = Is Null).

IntermediateQueryB SQL looks like this:

SELECT [ORDERS].PARTNUM
FROM [ORDERS] LEFT JOIN [FILTER] ON [ORDERS].PARTNUM = [FILTER].PARTNUMFILTER
WHERE ((([ORDERS].PARTNUM) Is Not Null) AND (([FILTER].PARTNUMFILTER) Is
Null));

Lastly, IntermediateQueryB and existing table [TOGGLE].PARTNUMTOGGLE are
then used in FinalQueryC which returns the effective list of part numbers.

FinalQueryC SQL looks like this:

SELECT
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER)) AS [FinalQueryC]
FROM [TOGGLE], [FILTER], [IntermediateQueryB], [IntermediateQueryA]
GROUP BY
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER))
HAVING
(((IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER))) Is Not Null))
ORDER BY
IIf([TOGGLE].PARTNUMTOGGLE=1,[IntermediateQueryA].PARTNUM,IIf([TOGGLE]=2,[IntermediateQueryB].PARTNUM,[FILTER].PARTNUMFILTER));

FinalQueryC gets me to my intended bottom line.

I'm seeing a LIKE clause, but I'm not seeing any wildcards. Could that be it?

[quoted text clipped - 30 lines]

Kevin