Conditional query criteria in Access 2003 with SQL Server BE

Hello:
I just upsized an Access 2003 application to a SQL Server 2000 back end.
There is a query that worked fine before the upsize, but does not work now.
The purpose of the query is to give me a list of employees that are
scheduled to work today. The employee master row has 7 checkboxes for the
days of the week.
Here is the SQL for the query. Can someone tell me how to make this work
with the SQL Server BE?
SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
FROM EmployeeQry
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
(((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
(((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
(((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
(((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
(((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
Thanks
Steve
John:
Thanks for the reply. Sorry ... the query returns no rows, when in fact
there are many for each day of the week. I tried your suggestions ...
comments under each suggestion.
Steve
"John Spencer" wrote:

> I would try:
> Adding to the IIF statement.
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True,False)))
>
I tried this with no difference

> Or even simpler, drop the IIF statement
> EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')
>
This returned all rows of the table.

> IF that still fails then try reversing the logic of the test. Just in
> case SQL server is storing the data as 1 (True) and 0 (False).
> EmployeeQry.Tuesday =IIf(Format(Date(),"dddd")<>'Tuesday',False, True)
>
Didn't work either.

> By the way "..., but does not work now." is not very descriptive of the
> problem. Does that mean, you get the wrong results, no results, an
> error, different results than expected at times, a syntax error or ...?
> Please try to be a bit more specific in describing the problem.
> '================================================= ===
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '================================================= ===
>
> Steve Happ wrote:
>
|||What type is EmployeeQry.Monday on SQL Server?
It could be as John alluded to that
for SQL Server
True = 1
False = 0
for Access
True = -1
False = 0
I might misunderstand, but you try "1"
instead of "True"
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
"Steve Happ" wrote:
> I just upsized an Access 2003 application to a SQL Server 2000 back end.
> There is a query that worked fine before the upsize, but does not work
> now.
> The purpose of the query is to give me a list of employees that are
> scheduled to work today. The employee master row has 7 checkboxes for the
> days of the week.
> Here is the SQL for the query. Can someone tell me how to make this work
> with the SQL Server BE?
> SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
> EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
> EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
> EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
> FROM EmployeeQry
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
> (((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
> (((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
> (((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
> (((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
> (((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
>
> Thanks
> Steve
|||or
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,Null))) OR
the point being that if Monday = "True"
on SQL, then its value is 1
which will never be equal to Access "True"
which is equal to -1
"Gary Walter" wrote:
> What type is EmployeeQry.Monday on SQL Server?
> It could be as John alluded to that
> for SQL Server
> True = 1
> False = 0
> for Access
> True = -1
> False = 0
> I might misunderstand, but you try "1"
> instead of "True"
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
>
> "Steve Happ" wrote:
>