Query from Form when "Not Like"

[Guest]

I have a form named MainForm in which I have a couple of unbound fields. I have a query named MainQuery in which I have criteria referring to those unbound fields in MainForm

For example, one of the unbound fields in Mainform is called Location. In my database, I have a field called NA. In my MainQuery, I want that NA field to refer to that unbound Location field in MainForm. Following some great examples in this newsgroup, I've entered this for the criteria for the NA field in MainQuery
[Forms]![MainForm]![Location

The NA field can have any one of 5 letters in it: "e", "x", "v", "w" or "z

When I enter any of these 5 letters in the Location field in MainForm and run MainQuery, everything comes out perfectly

However, I often need to run the query excluding "v". If I enter "<>v" in the Location field in MainForm and run MainQuery, it doesn't work. Short of going into MainQuery and manually changing the criteria to Not Like "v" each time I need to do this and then changing it back when I'm done, is there any way I can accomplish this either by some different syntax in criteria, or altering something in the unbound Location field in MainForm or perhaps some "if" statement in MainQuery

Many thanks
Paul Simon

 

[Michel Walsh]

Hi,



If you have a check box that is (normally) checked for a "equal" and
elect to uncheck it to ask for a "not equal", you can make a test like:


WHERE FORMS!FormNameHere!CheckBoxName = (
FORMS!FormaNameHere!Location = NA )



So, if the check box is true, checked, you want TRUE = ( location = NA )
and if the check bos ix false, you want: FALSE = ( location =
NA),
which is the same as:

TRUE= ( location <> NA)


That syntax AND that logic work only with JET.

Hoping it may help,
Vanderghast, Access MVP

I have a form named MainForm in which I have a couple of unbound fields.

I have a query named MainQuery in which I have criteria referring to those
unbound fields in MainForm.

For example, one of the unbound fields in Mainform is called Location. In

my database, I have a field called NA. In my MainQuery, I want that NA
field to refer to that unbound Location field in MainForm. Following some
great examples in this newsgroup, I've entered this for the criteria for the
NA field in MainQuery:

[Forms]![MainForm]![Location]

The NA field can have any one of 5 letters in it: "e", "x", "v", "w" or "z"

When I enter any of these 5 letters in the Location field in MainForm and

run MainQuery, everything comes out perfectly.

However, I often need to run the query excluding "v". If I enter "<>v" in

the Location field in MainForm and run MainQuery, it doesn't work. Short of
going into MainQuery and manually changing the criteria to Not Like "v"
each time I need to do this and then changing it back when I'm done, is
there any way I can accomplish this either by some different syntax in
criteria, or altering something in the unbound Location field in MainForm or
perhaps some "if" statement in MainQuery?

 

[Mark Burns]

Paul,

If you want to use a parameterized version you could try something like:
Parameter: [Enter Valid Letter Codes:], text;
Select <Whatever FROM Whatever> WHERE
instr([Enter Valid Letter Codes:],[NA]) <> 0;

<warning: air code caveats apply - check instr() syntax>
- Mark

I have a form named MainForm in which I have a couple of unbound fields.

I have a query named MainQuery in which I have criteria referring to those
unbound fields in MainForm.

For example, one of the unbound fields in Mainform is called Location. In

my database, I have a field called NA. In my MainQuery, I want that NA
field to refer to that unbound Location field in MainForm. Following some
great examples in this newsgroup, I've entered this for the criteria for the
NA field in MainQuery:

[Forms]![MainForm]![Location]

The NA field can have any one of 5 letters in it: "e", "x", "v", "w" or "z"

When I enter any of these 5 letters in the Location field in MainForm and

run MainQuery, everything comes out perfectly.

However, I often need to run the query excluding "v". If I enter "<>v" in

the Location field in MainForm and run MainQuery, it doesn't work. Short of
going into MainQuery and manually changing the criteria to Not Like "v"
each time I need to do this and then changing it back when I'm done, is
there any way I can accomplish this either by some different syntax in
criteria, or altering something in the unbound Location field in MainForm or
perhaps some "if" statement in MainQuery?

 

[Guest]

Michel and Mark

Thank you both very much for your great advice in helping me solve this problem - very helpful. I appreciate it very much

Pau

 

[Mark Burns]

Michel and Mark,

Thank you both very much for your great advice in helping me solve this

problem - very helpful. I appreciate it very much.

Paul

Hey, no problem, just send is both a free CD and concert tickets when you
and Garfunkle get back together on tour! ;-)