Very Computer

I've been trying to figure out why the following syntax returns an error and
I gave up!  Could someone please look at the following and tell me what I'm
doing wrong?  The goal is to get a 0 returned when the value in
q.TimesheetStatus is Null.

Thanks a lot.

Following is the syntax.  The part that is causing the error is:
(IIF([q.TimesheetStatus]) Is Null,0,([q.TimesheetStatus]))
SQL = "SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
(IIF([q.TimesheetStatus]) Is Null,0,([q.TimesheetStatus])) FROM Users as u
LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus from Timedetails where
WeekEnding='"&WeekEnd&"') as q ON u.UserID = q.UserID ORDER BY
q.TimesheetStatus DESC, u.UserID "

And the error I'm getting is:
Microsoft JET Database Engine error '80040e14'
Syntax error in query expression '(IIF([q.TimesheetStatus]) Is
Null,0,([q.TimesheetStatus]))'.

Re-write as:

SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
       COALESCE(q.TimesheetStatus, 0)
  FROM Users as u
  LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus
               FROM Timedetails
              WHERE WeekEnding = '"&WeekEnd&"') AS q
    ON u.UserID = q.UserID
 ORDER BY q.TimesheetStatus DESC, u.UserID

--
- Anith
( Please reply to newsgroups only )

I have tried it and I'm getting the following error:

error '80004005'
Unspecified error

I'm using Microsoft Access as the back-end database would that be why?

Thanks,

Quote:> Re-write as:

> SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
>        COALESCE(q.TimesheetStatus, 0)
>   FROM Users as u
>   LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus
>                FROM Timedetails
>               WHERE WeekEnding = '"&WeekEnd&"') AS q
>     ON u.UserID = q.UserID
>  ORDER BY q.TimesheetStatus DESC, u.UserID

> --
> - Anith
> ( Please reply to newsgroups only )

Is this SQL Server or MS Access? Which interface are you using to execute
the query?

--
- Anith
( Please reply to newsgroups only )

It's MS Access and I'm using ASP to execute the query.
Thanks.

Quote:> Is this SQL Server or MS Access? Which interface are you using to execute
> the query?

> --
> - Anith
> ( Please reply to newsgroups only )

I am not much familiar with Access, you may get a much better response, if
you post this in MS Access Newsgroup. My response was based on the
assumption that you are using SQL Server since you posted on this Newsgroup.

--
- Anith
( Please reply to newsgroups only )