Convert Access Query to SQL2000 Query

[Nuthan]

Hi,
How to convert the following access queries to SQL2000 compatible:

IIf([Time_Enroute] Is Not Null,IIf([Priority_Number]<3,"Code3","Code2" to
SQL server compatible query?

IIf([Time_Enroute] Is Not Null,IIf([Priority_Number]<3,"Code3","Code2" to
SQL server compatible query?

IIf([Transport_Protocol] Is Not Null,[Transport_Protocol],IIf(
Response_Vehicles_Assigned!Call_Disposition Is Not
Null,Response_Vehicles_Assigned!Call_Disposition,Response_Master_Incident!Call_Disposition)) AS Disposition,

Mid$([Disposition],1,5) AS Disp_Trim1,

Thanks for the help in advance.
Thanks,
Nuthan

 

[Tom Ellison]

Dear Nuthan:

Your code seems incomplete.

IIf([Time_Enroute] Is Not Null,IIf([Priority_Number]<3,"Code3","Code2"

This does not have closing parens, and needs more arguments, possibly:

IIf([Time_Enroute] Is Not Null, IIf([Priority_Number] < 3, "Code3",
"Code2"), MissingArgument)

That could code as:

CASE WHEN Time_Enroute IS NULL
CASE WHEN Priority_Number < 3 THEN 'Code3' ELSE 'Code2' END
ELSE MissingArgument END

or perhaps you mean:

IIf([Time_Enroute] Is Not Null, [Time_Enroute], IIf([Priority_Number]
< 3, "Code3", "Code2"))

which could be:

ISNULL(Time_Enroute,
CASE WHEN Priority_Number < 3 THEN 'Code3' ELSE 'Code2' END)

This is repeated in your post.

The other requests:

ISNULL(Transport_Protocol,
ISNULL(Response_Vehicles_Assigned.Call_Disposition,
Response_Master_Incident!Call_Disposition)) AS Disposition

(This one would be more easily written for Jet with Nz very similar to
the above.)

SUBSTRING(Disposition, 1, 5) AS Disp_Trim1

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Jamie Richards]

It may be helpful if you post the "context" in which this expression is
required to be used.

Jamie
Server side anti spam rules are enforced and ALL unsolicited email is
deleted.