View Single Post
  #10 (permalink)  
Old April 11th, 2004, 02:07 AM
mateenmohd mateenmohd is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Quote:
quote:Originally posted by jurgenw
 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
jwelz@hotmail.com
Reply With Quote