there is any other cluase etc. available in
of CASE clause in ACCESS ?

I am using following query in Access Report but it query give
error message on CASE cluse.

my table save are in SQL Server 2000, I use this query
in ACCESS Report.
this query run properly on sql server but how can
use this query in ACCESS ?

Please guide what cluase can be use in place of CASE clause
in the following query ?


SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno
order by
 CASE assignments.aposit
  WHEN 'Sr. Resident Engineer' THEN 1
  WHEN 'Resident Engineer' THEN 2
  WHEN 'Asst. Resident Engineer' THEN 3
  WHEN 'Quantity Suveyor' THEN 4
  WHEN 'Inspector (As Built)' THEN 5
  WHEN 'Surveyor' THEN 6
  ELSE 7
 END,
assignments.aposit


Please help.

Mateen

Use it as a pass trough query, then set it as the source for your report. Access does not support case statements, only the IIf function.



Sal

You colul use only the iif(iif, , ) , , ) instruction

Use SQL Server. You said it works in SQL Server, plus it is faster than using Jet.



Sal

thanks for your response.

Please give me one example of iif function.
how can use iif function in this query

SELECT employees.name, employees.posit
FROM employees
ORDER BY employees.posit;



I want to display the records like this.

order by
 CASE employees.posit
  WHEN 'Sr. Resident Engineer' THEN 1
  WHEN 'Resident Engineer' THEN 2
  WHEN 'Asst. Resident Engineer' THEN 3
  WHEN 'Quantity Suveyor' THEN 4
  WHEN 'Inspector (As Built)' THEN 5
  ELSE 6
 END,
employees.posit


Regards

Mateen

Straight from access help

IIf(IsNull([UnitPrice]), 0, [UnitPrice])
Changes a Null value to a zero (0) in the UnitPrice field

_____________________

You would have to nest it quite a bit.

But if you use this, you will be slowing down your processes by not using SQL Server engine.

Or you could send the values to the report without the case statement and put this code on the format event of the report.

select case true
 CASE aposit = 'Sr. Resident Engineer'
                txtValue = 1
  CASE aposit = ''Resident Engineer'
                txtvalue = 2
  case . . . . .
END SELECT

that way you do not have to use Jet.



Sal

thanks for your response.

I put this code on the format, it give syntax error
Please guide me how can iif function

or

select case true
 CASE aposit = 'Sr. Resident Engineer'
                txtValue = 1
  CASE aposit = ''Resident Engineer'
                txtvalue = 2
  case . . . . .
END SELECT


in the Access Report ?

Regards.

Mateen

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno
Order By IIF(assignments.empno='Sr. Resident Engineer', 1, IIF(assignments.empno= 7,'Resident Engineer', IIF (assignments.empno='Asst. Resident Engineer', 3, IIF(assignments.empno='Quantity Suveyor', 4, IIF(assignments.empno='Inspector (As Built)', IIF(assignments.empno='Surveyor', 6, 7))))))

You could alternatively Select on Where clauses for each type and select an integer for each type and union the selects together and order by the integer or you could add a sort field to the table to make life really easy, or join a sort table comprised of the empno field with the sort value.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Select Case aposit
    Case "Sr. Resident Engineer"
        txtValue = "1"
        Case "Resident Engineer"
        txtValue = 2
'insert the remaining values here
End Select

Or you can still try to use the IIf function and if you ever want to add another value, you will have a nightmare to make sure that the code will work. And the IIf statement will slow down your app.

Make sure that you have a text box that has the name txtValue (I should not have to say that)



Sal

Thanks for response.
I use your query.
it give error that

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '='.

hence I use query to remove '='


SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract,
assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments
ON employees.empno = assignments.empno
Order By IIF(assignments.empno,'Sr. Resident Engineer', 1,
IIF(assignments.empno,'Resident Engineer',2,
IIF(assignments.empno,'Asst. Resident Engineer', 3,
IIF(assignments.empno,'Quantity Suveyor', 4,
IIF(assignments.empno,'Inspector (As Built)',5,
IIF(assignments.empno,'Surveyor', 6)))));


it give error on IIF function

Server: Msg 195, Level 15, State 10, Line 10
'IIF' is not a recognized function name.

Thanks to Sal
Sorry I could not understand
how to put this code to format, and textbox name txtvalue
I also try your instructions as you mention.


select case true
 CASE posit = 'Resident Engineer'
                txtValue = 1
  CASE posit = 'Surveyor'
                txtvalue = 2
END SELECT



regards.

Mateen