This may be a simple question.

We recently converted our backend database from Access 97 to MS SQL
2000.

I have a huge reporting database (Access 2002) with thousands of
queries that I have been using for years when the database was in
Access 97. Now I am linking ODBC to the SQL Server.

There's the background - here's the question:

To query a Yes/No field I would normally use the criteria "0" (ignore
the quotes) for a "No" value, and the criteria "-1" for a "Yes" value.

Now it seems that the SQL database will only return records with a
"Yes" value if I use "1" instead of "-1" for the criteria in the
query.

Is there a way to get around this or do I have to go through hundreds
of queries to find instances of "-1"? Please tell me that I don't...

Thanks for the help

Marc Jones
IT Manager
Retail Ready Foods Inc.
mjones@retailready.com

Get Speed Ferret to do the work for you. An indispensable tool if
you're working with a large Access FE.

There's no way you can reasonably expect SQL Server to behave the same
way Jet did -- they are two different engines entirely. If someone
told you upgrading the data would be painless and that you wouldn't
have to modify your application, then they lied (or were similarly
misled themselves).

FWIW, your reports would run more efficiently if they were based on
stored procedures instead of queries. Either way you're looking at
rewrites, so you might as well go for the optimal solution.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On 14 Aug 2003 05:19:51 -0700, mjones@retailready.com (Marc Jones)
wrote:

>This may be a simple question.
>
>We recently converted our backend database from Access 97 to MS SQL
>2000.
>
>I have a huge reporting database (Access 2002) with thousands of
>queries that I have been using for years when the database was in
>Access 97. Now I am linking ODBC to the SQL Server.
>
>There's the background - here's the question:
>
>To query a Yes/No field I would normally use the criteria "0" (ignore
>the quotes) for a "No" value, and the criteria "-1" for a "Yes" value.
>
>Now it seems that the SQL database will only return records with a
>"Yes" value if I use "1" instead of "-1" for the criteria in the
>query.
>
>Is there a way to get around this or do I have to go through hundreds
>of queries to find instances of "-1"? Please tell me that I don't...
>
>Thanks for the help
>
>Marc Jones
>IT Manager
>Retail Ready Foods Inc.
>mjones@retailready.com