Bit Data Type Question

I have posted in Access Queries with no success. Thought I would try here.
I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
upsize wizard. I have a Access 2003 front end with linked tables to the SQL
backend.
I have a table with a bit data type field in the backend. When I append new
records to that table with the default value for the bit data type set to 0,
my Access front end queries do not return that data. If I manually edit the
data and replace the zero that I see there with another zero, then my queries
will work. Does anyone know why this is true? (I don't want to have to do
that of course!)
Can you use smallint instead bit datatype?
AMB
"tkosel" wrote:

> I have posted in Access Queries with no success. Thought I would try here.
> I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
> upsize wizard. I have a Access 2003 front end with linked tables to the SQL
> backend.
> I have a table with a bit data type field in the backend. When I append new
> records to that table with the default value for the bit data type set to 0,
> my Access front end queries do not return that data. If I manually edit the
> data and replace the zero that I see there with another zero, then my queries
> will work. Does anyone know why this is true? (I don't want to have to do
> that of course!)
|||I suppose I could, but would rather know why this is happening. It doesn't
make any sense to me and is driving me crazy!!! (The upsize wizard converted
my Access Yes/No data type to a SQL Bit type.)
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you use smallint instead bit datatype?
>
> AMB
> "tkosel" wrote:
|||Additionally,
When I run the following query i get no data returned:
SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
Absences.Hours_Missed, Absences.Absence, Absences.Comments,
Absences.Reason_Code, Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code)="F") AND
((Absences.FMLA_Transaction_Complete)=False));
If I run this query first, then the query above returns the record(s):
UPDATE Absences SET Absences.FMLA_Transaction_Complete =
[FMLA_Transaction_Complete]+0;
Weird eh?
"tkosel" wrote:
[vbcol=seagreen]
> I suppose I could, but would rather know why this is happening. It doesn't
> make any sense to me and is driving me crazy!!! (The upsize wizard converted
> my Access Yes/No data type to a SQL Bit type.)
> "Alejandro Mesa" wrote:
|||This definitely seems to be an access issue, not a SQL Server issue. Some comments:

> If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work.
You can use Query Analyzer and do a SELECT from the table to see if there is any difference.

> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
Above wouldn't even compile in SQL Server, it would return an error message. Unless there is a
column in the table named false. In addition, you enclose a string in double quotes which by default
will also generate an error (unless in above example there exist a column named F). So, probably
Access does something with the query before submitting it to SQL Server. You can use Profiler and
see what is submitted.
You will probably get better response to this in an Access group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tkosel" <tkosel@.discussions.microsoft.com> wrote in message
news:97025794-84BF-44F3-B201-A4AA54515A71@.microsoft.com...[vbcol=seagreen]
> Additionally,
> When I run the following query i get no data returned:
> SELECT Absences.Date, Absences.[Clock#], Absences.Hours_Here,
> Absences.Hours_Missed, Absences.Absence, Absences.Comments,
> Absences.Reason_Code, Absences.FMLA_Transaction_Complete
> FROM Absences
> WHERE (((Absences.Reason_Code)="F") AND
> ((Absences.FMLA_Transaction_Complete)=False));
>
> If I run this query first, then the query above returns the record(s):
> UPDATE Absences SET Absences.FMLA_Transaction_Complete =
> [FMLA_Transaction_Complete]+0;
> Weird eh?
> "tkosel" wrote:
|||There are a few problems regarding the bit datatype in SQL Server
tables that are accessed through linked tables from Microsoft Access,
so using a smallint instead of a bit is a good workaround (suggested by
Microsoft, too):
http://support.microsoft.com/kb/280730
http://support.microsoft.com/kb/318882
Razvan
|||On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:

>I have posted in Access Queries with no success. Thought I would try here.
>I have a MS SQL 2000 DB backend that was upsized from Access 97 using the
>upsize wizard. I have a Access 2003 front end with linked tables to the SQL
>backend.
>I have a table with a bit data type field in the backend. When I append new
>records to that table with the default value for the bit data type set to 0,
>my Access front end queries do not return that data. If I manually edit the
>data and replace the zero that I see there with another zero, then my queries
>will work. Does anyone know why this is true? (I don't want to have to do
>that of course!)
Hi tkosel,
From the "tossing around some ideas department":
Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
column in the offending rows is not 0 but NULL? Access will (IIRC)
represent a bit column as a yes/no column and has no way to repressent
the NULL that is also allowed in a SQL Server bit column - maybe it
chooses to represent it as No, though it won't match on a search for
rwos with the bit value equal to No?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
You were correct. In the table view of enterprise manager as well as table
and query views of Access, the value is shown as a zero. However with Query
Analyzer it is shown as a null. I learned a valuable lesson, don't trust
anything other that results garnered by Query Analyzer for sure. Anyhow,
thanks.
"Hugo Kornelis" wrote:

> On Thu, 18 Aug 2005 08:53:05 -0700, tkosel wrote:
>
> Hi tkosel,
> From the "tossing around some ideas department":
> Do a SELECT ... FROM ... in Query Analyzer. Could it be that the it
> column in the offending rows is not 0 but NULL? Access will (IIRC)
> represent a bit column as a yes/no column and has no way to repressent
> the NULL that is also allowed in a SQL Server bit column - maybe it
> chooses to represent it as No, though it won't match on a search for
> rwos with the bit value equal to No?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>