Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 7th, 2004, 05:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default case clause in Access

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


 
Old April 7th, 2004, 08:38 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 7th, 2004, 09:35 AM
Registered User
 
Join Date: Apr 2004
Location: Monterrey, Nuevo Leon, Mexico.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You colul use only the iif(iif, , ) , , ) instruction
 
Old April 7th, 2004, 10:48 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
 
Old April 8th, 2004, 01:48 AM
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 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




Quote:
quote:Originally posted by manuel
 You colul use only the iif(iif, , ) , , ) instruction
 
Old April 8th, 2004, 07:01 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 10th, 2004, 12:26 AM
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 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


Quote:
quote:Originally posted by sal
 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
 
Old April 10th, 2004, 12:07 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 10th, 2004, 07:44 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 11th, 2004, 02:07 AM
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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Case in Where Clause acko SQL Server 2000 12 September 12th, 2011 01:43 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 10:03 AM
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 12:45 PM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 05:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.