Linked Table Query Problems (SQL Server ODBC)

[Guest]

I already posted this once, but perhaps didn't phrase it right. I have an
Access 2003 Application that contains a linked table from a SQL Server 2000
Database. (It was upsized from Access 97.)

The following Query SQL yields many records.

SELECT Absences.ID, Absences.Date, Absences.Reason_Code,
Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code) Like "f"))
ORDER BY Absences.Date DESC;

I have included the top 4 records.

ID Date Reason_Code FMLA_Transaction_Complete
147169 8/15/2005 F 0
146858 4/12/2005 F -1
146809 4/12/2005 F -1
146976 4/12/2005 F -1

Reason_Code is a Bit Data type field in SQL. Since I am only interested in
records where the reason code is F and the FMLA_Transaction_Complete is
False, I changed the query. If I add the following to the Query, I get no
records returned.

SELECT Absences.ID, Absences.Date, Absences.Reason_Code,
Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code) Like "f") AND
((Absences.FMLA_Transaction_Complete)=False))
ORDER BY Absences.Date DESC;

Interesting note. If I open the linked table in table view in access and
filter on the FMLA_Transaction_Complete field for False, it returns the right
records.

Another interesting note, if I Import the data rather than link it, the
query works properly.

Can anyone help me? I am stuck, have wasted too much time on this!

 

[Guest]

Try and change the query, instead of false use 0, and also there is no point
in using Like if the filter is for the whole string and not for a part of a
string

SELECT Absences.ID, Absences.Date, Absences.Reason_Code,
Absences.FMLA_Transaction_Complete
FROM Absences
WHERE (((Absences.Reason_Code) = "f") AND
((Absences.FMLA_Transaction_Complete)=0))
ORDER BY Absences.Date DESC;

 

[Guest]

I had already tried using 0 instead of false, same results. It also doesn't
appear to make any difference if I use Like in the filter, I am using it
because there is a possibility there is a "F" in the value.

 

[Guest]

Try and change the query to a pss through query, to see what the result you
getting running te query on the sever.

 

[Guest]

Results were the same with a pass through Query. However, I have noticed
something interesting. If I use the date field to find the affected records,
then simply select the zero value in the FLMA_Transaction_Complete field and
type a zero again then build my query with the desired criteria again, it
will find the records. The records were originally created in the table with
an append query and the default value for FMLA_Transaction_Complete is zero.
Any ideas as to why the bit data doesn't seem to work until I manually edit
it?