query works, and doesn't work...

[madEG]

Ok, It seems that I am hitting a wall here writing some queries to analyze this mdb I was given to fix up...

SELECT *
FROM qryMGG_CustomersNotTrends
WHERE customers.username not like '%@%';

...works fine, but removing the NOT in NOT LIKE...

SELECT *
FROM qryMGG_CustomersNotTrends
WHERE customers.username like '%@%';

Does not...

Is there something fundamental I am missing as to why some valid sql queries work, and some do not? I tried it against a query and a proper table and the results are the same. This works both ways (using tables not queries) in SQL Server, so I know the syntax is ok.

Is there some manual about what sql queries that access doesn't support? Is there a way to have access return an error rather than no records for queries it can't run?

Please - I am not slagging access, just trying to understand what is going on.

Thanks!
-Matt G.

 

[Guus2005]

replace % with *

HTH

 

[Banana]

Just to exposit on Guss2005's suggestion, Jet's SQL is slightly different (well, so is every RDBMS vendor's but that's other story) from the SQL standard.

Thus:

String delimiter: "
Date delimiter: #
Wildcard: *

Among couple other things.

If you want to adhere to SQL standard, you can turn on option to enable ANSI 92 SQL compatibility, which will then have Jet follow the SQL standard. It's in Tools -> Options, under General tab, IIRC. Be aware this may break other queries you've created via query builder which will write Jet's SQL and may need to be rewritten, though.

 

[madEG]

Thanks guys! I think I'll do some more testing with the sql92 standard turned on...

Thanks for the tip!

-MattG.