sql server mseq: Hi

I am running an SQL server with an MS Access front-end.
One of my main forms is a list, which have a number of
calculated fields within the query that sits behind it (in
Access, not in SQL server).

I would like to bring these calculations into a View in
SQL server, to speed things up as I think this is what is
causing this list to hang for a while when it first opens.

The two calcuations are as follows:

DaysOnHold: IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])

WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))

I've discovered that the functions used above are
incompatible with SQL server (I tried creating them
in 'Views' within Enterprise Manager but gave me all sorts
of errors.)

If anyone could assist with the correct phrasing of the
above for the VIews, I'd be extremely grateful!!

Thanks

hi russell,

since the im not sure what is your exact expression in the query , by
doing some assumption you can convert existing IIF conditions using CASE and
ISNULL functions.

IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])

above expression can be converted to SQL server using isnull condition.
Ex:
isnull( [DateUnsuspended]-[DateSuspended], 0)

WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))

above expression can be converted to SQL server using CASE expression.
Ex:

case when [DateClosed] is null then
( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
else
( datepart(dd,[DateClosed]) - [JobVacant]-[DaysOnHold])/7 end

Look in books online on the topics "date functions", CASE, ISNULL

--
Vishal Parkar
vgparkar@yahoo.co.in

Hi Vishal

Thanks for your help. However, I have tried to enter this
expression in a View Column, but get the following error
message:

The Query Designer does not support the CASE SQL construct.

What does this mean? Is there another way to create views
which will allow me to use this CASE function?

Thanks

Russell

[quoted text, click to view]