Query criteria based on other field's value

The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a string.

Is it possible to create such a criteria?

Thank you.
John

NOT LIKE "*" & [OtherFieldFromSameTable] & "*"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"John J." wrote in message
...
The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a string.

Is it possible to create such a criteria?

Thank you.
John

I'm not sure what you're asking.

First, the criteria NOT LIKE "testvalue" is the same as "testvalue"
since there are no wildcards included.

Second, you *can* use a field in place of the literal value, like this ...

NOT LIKE "*" & [OtherField] & "*"

Add the wildcards in quotes and ampersand them to the field in brackets.
I don't see why that won't work for you.
--
Danny J. Lesandrini

www.amazecreations.com


"John J." wrote ...
The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a string.

Is it possible to create such a criteria?

Thank you.
John

Try:
Not Like [OtherFieldFromSameTable] & "*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John J." wrote in message
...
The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a string.

Is it possible to create such a criteria?

On Sun, 01 Mar 2009 16:01:29 +0100, John J. wrote:

The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a
string.

Is it possible to create such a criteria?

Not Like [OtherFieldFromSameTable] & "*"


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks all. I accidentally tested on the wrong table. Finally:

Not Like [OtherFieldFromSameTable] & "*"

does the job.

John


"John J." schreef in bericht
...
The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a string.

Is it possible to create such a criteria?

Thank you.
John

"John J." wrote in message
...
Thanks all. I accidentally tested on the wrong table. Finally:

Not Like [OtherFieldFromSameTable] & "*"

does the job.

John


"John J." schreef in bericht
...
The following criteria in a query works: Not Like "testvalue*"

Now, I would like to substitute testvalue for the value of another field
from the same table, something like:
Not Like "[OtherFieldFromSameTable]*"

Obviously this doesn't work as the portion after Like is seen as a
string.

Is it possible to create such a criteria?

Thank you.
John