Conditional query criteria in Access 2003 with SQL Server BE

[Guest]

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 Spencer]

I would try:

Adding to the IIF statement.

(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")='Tuesday',True,False)))


Or even simpler, drop the IIF statement

EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')

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)

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
'====================================================

 

[Guest]

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

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.

 

[Gary Walter]

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

 

[Gary Walter]

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