Using Wildcards

  • Thread starter Thread starter Ryan Langton
  • Start date Start date
R

Ryan Langton

I recently converted a database from an .mdb to .adp and am having problems
with my queries that use wildcards. Here is an example query in my old
database:

SELECT *
FROM tblUnitDescription
WHERE (Issued_To NOT LIKE "*storage*");

In the .adp, this does not work. I get an error that "*storage*" is an
invalid column. When I try to edit in Design View, my Criteria field is
changed by access to:

NOT LIKE N'[*storage*]'

This, however, generates the same error.
Any suggestions appreciated.
Thanks.
 
On SQL-Server, you must generally use single quote ' instead of double
quotes " to delimit your char strings. There is an option to override this
but obviously, it's probably not set up in your current configuration. Same
thing for the dates: use ' instead of # .

The N before the ' is for Unicode.
 
Hmm... SQL server is converting my query to single quotes, but this still
does not work. It is picking up every record, even if (storage) is
contained within the field being tested, so it appears that the WILDCARD (*)
characters are being taken literally. How do I test the condition WHERE
fieldA (DOES NOT CONTAIN TEXT) "storage"? So records containing "MY
storage", "storageBinC", and "sqlstoragetest" in fieldA would all be
filtered out.

In plain Access it is simple, please see my query example below. How do I
do it in .adp (SQL server)?

Thanks.

Sylvain Lafontaine said:
On SQL-Server, you must generally use single quote ' instead of double
quotes " to delimit your char strings. There is an option to override
this but obviously, it's probably not set up in your current
configuration. Same thing for the dates: use ' instead of # .

The N before the ' is for Unicode.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Ryan Langton said:
I recently converted a database from an .mdb to .adp and am having
problems with my queries that use wildcards. Here is an example query in
my old database:

SELECT *
FROM tblUnitDescription
WHERE (Issued_To NOT LIKE "*storage*");

In the .adp, this does not work. I get an error that "*storage*" is an
invalid column. When I try to edit in Design View, my Criteria field is
changed by access to:

NOT LIKE N'[*storage*]'

This, however, generates the same error.
Any suggestions appreciated.
Thanks.
Click to expand...
Click to expand...
 
Oh, I'm sorry but I've also missed the fact that the wild card for
SQL-Server is % and not * .

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Ryan Langton said:
Hmm... SQL server is converting my query to single quotes, but this still
does not work. It is picking up every record, even if (storage) is
contained within the field being tested, so it appears that the WILDCARD
(*) characters are being taken literally. How do I test the condition
WHERE fieldA (DOES NOT CONTAIN TEXT) "storage"? So records containing "MY
storage", "storageBinC", and "sqlstoragetest" in fieldA would all be
filtered out.

In plain Access it is simple, please see my query example below. How do I
do it in .adp (SQL server)?

Thanks.

Sylvain Lafontaine said:
On SQL-Server, you must generally use single quote ' instead of double
quotes " to delimit your char strings. There is an option to override
this but obviously, it's probably not set up in your current
configuration. Same thing for the dates: use ' instead of # .

The N before the ' is for Unicode.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Ryan Langton said:
I recently converted a database from an .mdb to .adp and am having
problems with my queries that use wildcards. Here is an example query in
my old database:

SELECT *
FROM tblUnitDescription
WHERE (Issued_To NOT LIKE "*storage*");

In the .adp, this does not work. I get an error that "*storage*" is an
invalid column. When I try to edit in Design View, my Criteria field is
changed by access to:

NOT LIKE N'[*storage*]'

This, however, generates the same error.
Any suggestions appreciated.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top