Re: Migrating from Access, boolean field problem

From: Pavils Jurjans (pavils_at_mailbox.riga.lv)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 18:09:50 +0200

Thanks Mary,

This is a good suggestion, it helps me to move further...

Now another problem has arised:

Access runs this query without complaints:

UPDATE myTable SET myBool = NOT myBool WHERE id=100

What could be a syntax working on both Jet and MSSQL engines?

I tried this one...

UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100

but I get error -
'IIf' is not a recognized function name.

Maybe some other smart syntax?

Thanks,

-- Pavils

"Mary Chipman" <mchip@nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
>
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
>
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@mailbox.riga.lv> wrote:
>
> >Hallo,
> >
> >I am migrating complex database from Access 2000 to MSSQL server 8. This
> >database is used by web applications. Some time I need to be able swicth
> >between the old Access version and new SQL server driven one. I have
> >different connection strings, and connecting works well.
> >
> >However, some sql query incompatibility has appeared...
> >
> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
run
> >a query like this:
> >
> >SELECT id FROM myTable WHERE myBooleanField
> >
> >it returns all records where myBooleanField value is true. Now, when I
> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
for
> >every Access "true" value, and "0" for "false" values. With this new
field
> >type, I can no longer run the above mentioned query. I must change it to
> >this:
> >
> >SELECT id FROM myTable WHERE myBooleanField=1
> >
> >Now, this would not be a crazy problem, however I need to be able to run
it
> >for some time on both database solutions, Access and MSSQL, so I really
need
> >to have compatible queries.
> >
> >One solution I see now would be converting the field type in access from
> >boolean to byte, and then run the "=1" queries. But that could involve
range
> >of incompatibilities within the application itself, that assumes that
these
> >fields are "true" or "false" instead of "1" and "0". Browsing through the
> >application to find these places is not really the best thing I can
imagine.
> >
> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
approaches
> >to solve my problem?
> >
> >Thanks,
> >
> >Pavils
> >
>