Pass string to "Not In" parameter 1

[nickfatool]

I'm attempting to pass data from a form to a parameter
within a query.

In the criteria cell I've entered:
Not In ([Forms]![MyForm]![MyTextBox])

MyTextBox contains the literal string
"Stoves","Ovens"

Yet Stoves and Ovens are still returned.
Any Clues?

Thanks,

Rob Dexter.

 

[Steve101]

Not sure that you'll get that method to work (though I'd be interested if anyone has any other ideas.)

However, the following will emulate the functionality you require, and should achieve what you want:

I've abstracted the problem to have a table called Table1, which has field called f1. We're looking for all records in the table which are "NOT IN" the unbound control called Text0 in a form called Form1. The form is open and populated with comma separated values when the query is run:

The query looks like this:

SELECT Table1.f1
FROM Table1
WHERE InStr(Forms!Form1!Text0,f1) = 0;

the Instr function checks the value of the current f1 field in the query, looking for its starting position in the Forms!From1!Text0 control. If it doe'snt find it, the WHERE clause evaluates to TRUE, and the record gets returned.

For more info, check your online help to see how the InStr function works.

A couple of other points:

(a) If you want to emulate "IN" as opposed to "NOT IN", change the = symbol to > in the query.

(b) This method is not "foolproof" as coded above. For example it will find "AT" if the Text0 string contains "CAT, DOG"

There are a number of ways to prevent this sort of situation; for example, use single quotes to separate the entries in the form field, and change the select statement to:

SELECT "'" & Table1.f1 & "'"
FROM Table1
WHERE InStr(Forms!Form1!Text0,f1) = 0;

This would force the Instr to "match" the entire field.

Food for thought,

Hope this does the trick,

Cheers,
Steve

 

[nickfatool]

It's Genius like that that I was after....
It works an absolute treat.


Thanks Steve.

 

[Steve101]

No worries mate. Glad to help
Steve